Search code examples
excelexcel-formulavlookup

Comma-separated indices to lookup in a reference array

I have a column with a bunch of comma-separated numbers, like A1 has 43,91,21 B2 has 12,84 etc...

And then there is in another sheet of the workbook, a reference array with the above-mentioned indices in one column, and the color/material/usage each index corresponds to.

So, I'd like to calculate A2 to the values that correspond to each of the indices, like red,green,blue

I've searched online and all I can find is the exact opposite of what I need.

Is what I need easily achievable? From a programmer's perspective, I'd need to explode each index, look it up in the reference array, then concatenate the corresponding values.

In Excel is there any formula that does that for me?

Take for example these two arrays:

COLORS
44,26
95
35,45,21
23
Index Color
26 yellow
35 silver
44 blue
45 orange
57 purple
95 magenta

TIA!

Solution

  • Try the following formulas:

    enter image description here

    • Formula used in cell B2

    =IF(A2="","",TEXTJOIN(", ",1,XLOOKUP(TEXTSPLIT(A2,",")+0,E:E,F:F,"")))

    Or,

    enter image description here

    • Formula used in cell B2

    =IF(A2="","",TEXTJOIN(", ",1,XLOOKUP(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",100)),ROW($ZZ$1:INDEX($Z:$Z,LEN(A2)-LEN(SUBSTITUTE(A2,",",))+1))*100-99,100))/1,E:E,F:F,"")))