VLOOKUP with CONCATENATED result (col_index_num)

mrnewbie

New Member
Joined
May 20, 2014
Messages
10
Hi there,

I am trying to figure out how to perform a VLOOKUP and return a concatenated result from multiple columns on the matched row. Below is how I want the data to end up looking:

Result Worksheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Full Name[/TD]
[TD]Phone[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Joe Smith[/TD]
[TD]1234[/TD]
[TD]123 Gumdrop Lane, Gummie, Candyland[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sally May[/TD]
[TD]5678[/TD]
[TD]123 Snickers St., Cocoa, Candyland[/TD]
[/TR]
</tbody>[/TABLE]

Database Worksheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Full Name[/TD]
[TD]Phone [/TD]
[TD]Home Street[/TD]
[TD]Home City[/TD]
[TD]Home State[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Joe Smith[/TD]
[TD]1234[/TD]
[TD]123 Gumdrop Lane[/TD]
[TD]Gummie[/TD]
[TD]Candyland[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sally May[/TD]
[TD]5678[/TD]
[TD]123 Snickers St.[/TD]
[TD]Cocoa[/TD]
[TD]Candyland[/TD]
[/TR]
</tbody>[/TABLE]

The VLOOKUP for the single match in the "Phone" column is working fine.

Example for B2 in "Result" Worksheet:

Code:
=IF(A2<>"",VLOOKUP(A2,'Database'!$A$2:$E$3,2,FALSE),"")

Now for cell C2 in the "Result" worksheet I want to concatenate C2:E2 in the "Database" worksheet (adding commas and spaces in between). Can someone please advise? Thanks a million!

Jared
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Well your best bet is helper columns then, that do the vlookups-hidden off to the side. Then combine them in the column you want visible.

My formula's would work with a lot of nested IF's...I can't think of any other way at this point besides VBA, an add-in.
 
Upvote 0

Forum statistics

Threads
1,226,693
Messages
6,192,468
Members
453,726
Latest member
JoeH57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top