Excel – Allow a cell containing multiple delimited lookup source values to also contain non-lookup values

lookupmicrosoft excelworksheet-function

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

Please open the link to see the previously answered question.

Given that context, is there a formula that could directly return a text value located in column E instead of performing the lookup?

If column E contains a mix of numbers and text values, could the formula return both the name associated to the number via a lookup and the text value directly?

Example:

E3 contains 2;3;JohnnyF3 formula result B;C;Johnny

enter image description here

Best Answer

The new formula is a bit longer than the original one, as the MID(…) function has to be copied and used another two times.

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,
  IF(
    ISNUMBER(
      --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
      )
    ),
    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
        )
      ))
    ),
    TRIM(
      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
      )
    )
  )
)}

Note that the change in the formula is just an added IF() function that checks whether the extracted value is a number or text, and processes it differently. A text value is returned as-is, whilst a number value is used to perform a lookup just like before.



The modified simpler Excel 2016 (Windows only) formula is:

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