Search code examples
excelexcel-formulaxlookup

How to use a VLOOKUP or XLOOKUP when a cell has comma seperated values

I'm trying to find the ID for each value in a cell. I tried using a VLOOKUP but it fails when a cell has more than one value.

In this scenario, B2 would be updated to 4,1

enter image description here

enter image description here

Solution

  • TEXTJOIN with an IF:

    =TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH(","&SUBSTITUTE('Item Details'!$A$2:$A$6," ","")&",",","&SUBSTITUTE(A2," ","")&",")),'Item Details'!$B$2:$B$6,""))

    This will order the output on the lookup table order

    enter image description here

    Another option is using XLOOKUP:

    =TEXTJOIN(",",TRUE,XLOOKUP(FILTERXML("<a><b>"&SUBSTITUTE(A2,",","</b><b>")&"</b></a>","//b"),'Item Details'!A:A,'Item Details'!B:B,"",0))

    with Office 365 which will order by the input.

    enter image description here