Indefinite/ Multiple Vlookups until hit blank column.

[tgray1]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Vlookups?[/TD]
[TD]Col1[/TD]
[TD]Col2[/TD]
[TD]Col3[/TD]
[TD]Col4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]34[/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]29[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi All - I have the following table. First column is an ID. The following columns (ignoring Vlookups) are a number of additional IDs that relate to the IDs in the column. (e.g. IDs 34 and 45 are inputs for ID 1).

I'm looking for some formula that can vlookup the IDs in Col1, Col2, etc and return the Names of the ID - The Names of the ID are in another tab, simple two column list A = IDs, B = Names.

My difficulty is how to tell the vlookup up formula to keep adding the fomula until it hits a blank Column - e.g. no more related IDs.

For example,
row 1 will return "ID34 Name; ID45Name"
row 2 will return "ID20 name; ID25 name; ID29 name; ID30 name"
row 3 will return "ID10"

I'm not being lazy btw, some rows in my data have 45 related IDs (split into 45 columns) and other rows only have 2.

Thanks.

 

[Eric W]

Welcome to the forum.

Excel has a hard time concatenating variable size arrays. Using basic functions, you could create 45 additional columns to the right of the ID columns, each of which looks up the ID if it's there, and concatenates it to the results of the previous cell. Then column B would use the result of the last column.

If you have Excel 2016, or Excel Live, Microsoft added a new function called TEXTJOIN. Using that, you can get the results you want like this:

	A	B
ID	Name
Al
Brianne
Cal
Dyan
Ed
Felice
Greg
Hal

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]34[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]45[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]29[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]55[/TD]

</tbody>

Sheet2​

That's just the lookup sheet. Here's the formula:

	A	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Vlookups?[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Col1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Col2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Col3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Col4[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Cal; Dyan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]34[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]45[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Brianne; Ed; Felice; Greg[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]29[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Al[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=TEXTJOIN("; ",TRUE,IF(C2:G2=Sheet2!$A$2:$A$20,Sheet2!$B$2:$B$20&"",""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



In some basic testing, it works OK, but occasionally the order of the names doesn't match the order of the IDs.

If you don't have TEXTJOIN, or if the order is an issue, and you don't want to use the extra 45-column method, we'd have to write a UDF to do this. If you want to try that, let me know and I'll write it up.

Hope this helps.

 

[Mike Szczesny]

Can you use something like this? The first formula is to created the concatenated items in a cell. The next formula is to list the all the numbers for a given ID. In the result table, E2:E4 shows 1,2 and 3. The first concatenation formula is in F2. The formula is =concatenate(transpose(if(a2:a8=e2,c2:c6))). Highlight the transpose(........) part of your formula with your mouse. Hit F9. Remove the {, all the FALSES ";" and last }. Hit enter. The second formula is G2. The formula is [TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"] =INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=$E$2,ROW($B$2:$B$8)-ROW($B$2)+1),COLUMNS($G$2:G2))) You need to us Cntrl+Shift+Enter. Copy across

[TABLE="width: 751"]
<colgroup><col span="5"><col><col span="4"></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Number[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Number[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10[/TD]
[TD]Al[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Cal ; Dyan [/TD]
[TD]10[/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[TD]Brianne[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Brianne ; Ed ; Felice ; Greg [/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]29[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]34[/TD]
[TD]Cal[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Al [/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]45[/TD]
[TD]Dyan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]25[/TD]
[TD]Ed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]29[/TD]
[TD]Felice[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]30[/TD]
[TD]Greg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]