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
 
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,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

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