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

	A	B	C
1	Full Name	Phone	Address
2	Joe Smith	1234	123 Gumdrop Lane, Gummie, Candyland
3	Sally May	5678	123 Snickers St., Cocoa, Candyland

<tbody>
</tbody>

Database Worksheet

	A	B	C	D	E
1	Full Name	Phone	Home Street	Home City	Home State
2	Joe Smith	1234	123 Gumdrop Lane	Gummie	Candyland
3	Sally May	5678	123 Snickers St.	Cocoa	Candyland

<tbody>
</tbody>

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.