1

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

2

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")))}
  • Hero. Works delightfully. – Lucio Jun 28 '18 at 11:41
  • @Lucio Now that you've reached 15+ reputation, don't forget to upvote the answer as well ;-) (And all working/useful answers to your previous questions and future ones as well.) – robinCTS Jun 28 '18 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.