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

Solution 1:

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.