Indefinite/ Multiple Vlookups until hit blank column.

tgray1

New Member
Joined
Sep 19, 2017
Messages
2
[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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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:

AB
IDName
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:

ABCDEF

<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.
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top