Microsoft Excel – Lookup Multiple Values in Different Table

lookupmicrosoft excelworksheet-function

I have two tables, which look like this:

enter image description here

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.)


Worksheet Screenshot

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.

Related Question