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;Johnny`

→ `F3`

formula result `B;C;Johnny`

## 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.Array enter (

Ctrl+Shift+Enter) the following formula in`F2`

and copy-paste/fill-down into the rest of the column: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: