Lookup multiple values in a different table given a cell containing multiple delimited values

I have two tables, which look like this: I would like Excel to use the value in `ID company associated` in table 2 (green), look for it in table 1 (orange) and return the value from `Company name`, or multiple values when multiple companies are associated with the same person in table 2.

What would be the best solution?

Here's a formula that will work in Excel 2016, as is. In earlier versions of Excel, a poly-fill UDF for `TEXTJOIN()` is required. (See this post for a basic one.)

Array enter (Ctrl+Shift+Enter) the following formula in `F2` and copy-paste/fill-down into the rest of the column:

``````{=TEXTJOIN(";",TRUE,INDEX(B:B,N(IF(1,MATCH(--MID(SUBSTITUTE(E2,";",REPT(" ",99)),(ROW(OFFSET(\$A\$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)*99+((ROW(OFFSET(\$A\$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1)))=1),99),A:A,0)))))}
``````

Note that this formula only works if the values in column `A` are actually stored as numbers. For text values, the `--MID(…)` in the formula needs to be replaced by `TRIM(MID(…))`.

The prettified formula is as follows:

``````{=
TEXTJOIN(
";",
TRUE,
INDEX(
(B:B),
N(IF(1,
MATCH(
--MID(
SUBSTITUTE(E2,";",REPT(" ",99)),
99*(ROW(OFFSET(\$A\$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)
+(1=ROW(OFFSET(\$A\$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))),
99
),
(A:A),
0
)
))
)
)}
``````

Notes:

• The prettified formula actually works if entered.
• The brackets around `(A:A)` in the prettified version are required to force the `A:A` to remain on its own line. The same applies for the `(B:B)`.

For Excel 2016 (Windows only) the following simpler formula should work:

``````{=TEXTJOIN(";",TRUE,INDEX(B:B,N(IF(1,MATCH(--FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b"),A:A,0)))))}
``````

Just like the previous formula, this one also only works on values stored as numbers. For text values, just remove the `--` from the formula.

• Hi RobinCTS, thanks for the answer provided, it works tremendously. A last question related to the above: how do I have the formula to add a separator between names, say a semicolon between company name? Much appreciated. – Lucio Jun 15 '18 at 13:36
• @Lucio Umm, it already does that. That's what the first `;` in the formula is for. You can also change it to any other separator string. PS If you really appreciated my help, once you reach 15+ reputation don't forget to come back and upvote my answer as well ;-) – robinCTS Jun 15 '18 at 13:51
• At a first look, it seems like I'm getting only 1 value for every cell in column F (1 company name per cell in column F). I was expecting at least a few cells with multiple values separated by a semicolon. It is possible, but very unlikely given the data set I'm working with. I will investigate thoroughly the file and get back to you. PS: Will do. – Lucio Jun 15 '18 at 14:07
• @Lucio You need to array-enter the formula using `Ctrl`+`Shift`+`Enter`, instead of just `Enter`. (That's why the braces are there - to remind you to do so ;-) ) PS It isn't the cause of your issue, but for future reference I have added information to the answer concerning numbers stored as text versus numbers stored as actual numbers PPS I've also slightly simplified the non-prettified first formula. (The prettified version was already the simpler version.) – robinCTS Jun 15 '18 at 14:43
• Worked 100%. Thank you for the clarification. – Lucio Jun 15 '18 at 20:46