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

 

[mrnewbie]

The second formula does have { } around it. C2 and C3 are returning "123 Gumdrop Lane, Gummie"

 

[Slizer6893]

Alright I will try something else out, I can't see why this won't work.

 

[mrnewbie]

I can't upload my sample excel file, but I can at least give you a screenshot of the worksheets. Hopefully this helps!

Result Worksheet

Database Worksheet

 

[Slizer6893]

Copied your data exactly here our the formulas from C1 to C3

C1

{=IFERROR(INDEX(Database!$C$2:$C$5000,SMALL(IF(A2=Database!$A$2:$A$5001,ROW(Database!$A$1:$A$5000)-MIN(ROW(Database!$A$1:$A$5000))+1,""),ROW(Database!$A$1)))&", "&INDEX(Database!$D$2:$D$5000,SMALL(IF(A2=Database!$A$2:$A$5001,ROW(Database!$A$1:$A$5000)-MIN(ROW(Database!$A$1:$A$5000))+1,""),ROW(Database!$A$1)))," ")}

C2

{=IFERROR(INDEX(Database!$C$2:$C$5000,SMALL(IF(A3=Database!$A$2:$A$5001,ROW(Database!$A$1:$A$5000)-MIN(ROW(Database!$A$1:$A$5000))+1,""),ROW(Database!$A$1)))&", "&INDEX(Database!$D$2:$D$5000,SMALL(IF(A3=Database!$A$2:$A$5001,ROW(Database!$A$1:$A$5000)-MIN(ROW(Database!$A$1:$A$5000))+1,""),ROW(Database!$A$1)))," ")}

C3

{=IFERROR(INDEX(Database!$C$2:$C$5000,SMALL(IF(A3=Database!$A$2:$A$5001,ROW(Database!$A$1:$A$5000)-MIN(ROW(Database!$A$1:$A$5000))+1,""),ROW(Database!$A$1)))&", "&INDEX(Database!$D$2:$D$5000,SMALL(IF(A3=Database!$A$2:$A$5001,ROW(Database!$A$1:$A$5000)-MIN(ROW(Database!$A$1:$A$5000))+1,""),ROW(Database!$A$1)))," ")}

I changed the references slightly it looks like you were using phone number? to match I'm not sure. But try mine I used your data exactly, it should work if copy formula one into C1 and copy down that should work as well.

Let me know

 

[mrnewbie]

It is working now! I thought I could just Shift-click C2 and C3 in the Results worksheet then paste the formula and press Ctrl+Shift+Enter. I didn't realize I needed to copy a unique formula into each cell. Thanks for your help!

 

[Slizer6893]

Well you shouldn't have to copy a unique formula, you should be able to copy the formula down by dragging the box on the right corner.

Either way I am glad it works.

 

[platonic567]

It is working now!

I know your problem got solved but why not just concatenate Columns C, D,& E of the database into Column F like below:

=C2&", "&D2&", "&E2

After that simply vlookup column F i.e col_index_num = 6

 

[mrnewbie]

That's working too =) Thanks for the tip.

Last question Say Joe Smith's Home City field (D2 on the database worksheet) is blank. The current formula still shows a comma in C2 of the results worksheet with no data after it. Is there a way to remove the comma if a particular field in the database worksheet returns no data?

 

[Slizer6893]

We can nest some IF Statements in there but I do agree with platonic, its a lot simpler to use helper columns. But I believe this solution allows you to constantly use new data with the same formula if the format stays consistent.

I can nest IF statements into our currently formula but its just going to grow the formula bigger and harder to fix later. Up to you, I will write one out in a second.

 

[platonic567]

Is there a way to remove the comma if a particular field in the database worksheet returns no data?

Do you mean changing "123 Gumdrop Lane, , Candyland" to show "123 Gumdrop Lane, Candyland" ?

If yes, then use below formula in F2 and copy down:

=SUBSTITUTE(C2&", "&D2&", "&E2,", ,",",")

After that vlookup column F as previously mentioned