VLOOKUP with CONCATENATED result (col_index_num)

[mrnewbie]

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:

=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

 

[Slizer6893]

Yeah nesting to many IF statements is just going to cause problems. We can try VBA if you'd like.

 

[mrnewbie]

I'm happy without VBA. Thanks a bunch for working this through with me. Great learning experience!

 

[Slizer6893]

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.