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]

I mean the simple answer would be to use different vlookups in one formula and nest them with a "&" symbol

So with your formula

=IF(A2<>"",VLOOKUP(A2,[COLOR=#333333]'Database'!$A$2:$E$3[/COLOR],2,FALSE),"")&" ,"&IF(A2<>"",VLOOKUP(A2[COLOR=#333333]'Database'!$A$2:$E$3[/COLOR],3,FALSE),"")&" , "&IF(A2<>"",VLOOKUP(A2,[COLOR=#333333]'Database'!$A$2:$E$3[/COLOR],4,FALSE),"")

 

[mrnewbie]

Thanks, Slizer6893. I appreciate the help!

With a big database will this affect speed significantly? Do you know of any more efficient alternatives? My preference would be to minimize the number of search calls, but your solution works. Thanks again.

 

[Slizer6893]

Oh my this would be so slow on anything over a couple thousand rows I would think. What you want is an array formula, I'll try and write one up

 

[mrnewbie]

Got it. I figured it might be a bit laggy. If you're able to write up an array formula that would be terrific! I'm just getting into this stuff so having a formula will be a great learning tool for me. Thanks!

 

[Slizer6893]

I think I took this too far...but this should work? At least try it and then we can tweak the references and what not. Works with your mini data set and should work on LOTS of data too

Ctrl+Shift+Enter is how you enter this in as an array formula

=IFERROR(INDEX(database!D$1:D$4,SMALL(IF(B3=database!$B$1:$B$50,ROW(database!$B$1:$B$50)-MIN(ROW(database!$B$1:$B$50))+1,""),ROW(database!$A$1)))," ")&","&IFERROR(INDEX(database!E$1:E$4,SMALL(IF(B3=database!$B$1:$B$50,ROW(database!$B$1:$B$50)-MIN(ROW(database!$B$1:$B$50))+1,""),ROW(database!$A$1)))," ")&","&IFERROR(INDEX(database!F$1:F$4,SMALL(IF(B3=database!$B$1:$B$50,ROW(database!$B$1:$B$50)-MIN(ROW(database!$B$1:$B$50))+1,""),ROW(database!$A$1)))," ")

This is some ugly formula writing in my opinion but I tried and it should work.

 

[mrnewbie]

Thanks for the help. Unfortunately I am unable to get the above formula to work, even with the sample data set. Might you be able to cull down the array formula so that it's only grabbing the "Home Street" field from the "Database" worksheet? That way I can make sure I'm inputting the formula correctly, then I can add on the concatenated stuff. Thanks again.

 

[Slizer6893]

=IFERROR(INDEX(D$2:D$5,SMALL(IF(B6=$B$2:$B$51,ROW($B$1:$B$50)-MIN(ROW($B$1:$B$50))+1,""),ROW($A$1)))," ")

Alright let me break this down, it doesn't have your database reference but you can figure that much out, this formula would go into the address cell after Joe Smith and 1234.

Let's see if I can explain this better so you can adjust/fix it going forward.

=Iferror(INDEX(D$2:D$5)

First part, hopefully this is somewhat familiar. This is looking at only that section, obviously for more rows you will make the reference range much larger.


SMALL(IF(B6=$B$2:$B$51

Next part of the formula is taking the smallest point in which B6 is found in the range where the full name is found, basically a match. The way it works is Small(Array,K) where k is the lowest variable we want to look at. For this first part that would be the lowest possible value.

ROW($B$1:$B$50)-MIN(ROW($B$1:$B$50))+1

Where the formula starts to look at what has happened, it looks down the rows and subtracts the minimum row, which would be a row already used. Since this is the first step it will still look for the row with Joe Smith on it and not keep going down.

ROW($A$1)))," ")

Finally this is where the SMALL(K) comes from.

Try this on just your practice data, and then we can make it grow to include more and more.

 

[mrnewbie]

Thanks! It's starting to come together. I have it pulling data from the sample set. I simply needed to replace

SMALL(IF(B6=$B$2:$B$51

with

SMALL(IF(B2=$B$2:$B$51

and then add the worksheet reference. Now, to increase the range and concatenate the HOME STREET and HOME CITY fields, I'm using the following code:

[/COLOR]=IFERROR(INDEX('database'!$C$2:$C$5000,SMALL(IF(B2='database'!$B$2:$B$5001,ROW('database'!$B$1:$B$5000)-MIN(ROW('database'!$B$1:$B$5000))+1,""),ROW('database'!$A$1)))&", "&INDEX('database'!$D$2:$D$5000,SMALL(IF(B2='database'!$B$2:$B$5001,ROW('database'!$B$1:$B$5000)-MIN(ROW('database'!$B$1:$B$5000))+1,""),ROW('database'!$A$1)))," ")[COLOR=#333333]

The problem now is that it's not working as an array formula. When I use this formula across C2 and C3 (the ADDRESS fields in the Result worksheet) by hitting Ctrl+Shift+Enter, I get Joe Smith's address across both fields. Any tips on how to solve this?

 

[Slizer6893]

Umm, that seems odd. If you copy down the formula it will add to the array. You could enter the formula in the first cell and also select all the cells you want it in then hit CTRL+SHIFT+ENTER.

I tested your formula on my data and it worked fine so I'm not sure what you aren't doing

Does the second formula have { } around it?