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?

## Best Answer

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

Notes:`(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:

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

`--`

from the formula.