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

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

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;Johnny``F3` formula result `B;C;Johnny`

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

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