how to xlookup if the search keys are comma-separated.

Hello,
I have an excel sheet attached. I am trying to xlookup some search keys are comma-separated in excel.
What I would like to do is to SUM up all the returned values into a single cell. I dont know if this is possible for I have to use some VBA scripts.

I tried =XLOOKUP(F2,$A$1:$A$14,$B$1:$B$14)

Any ideas would be greatly appreciated.

a.JPG

hi,
you may have more than 2 items in one cell?

Originally Posted by belinda200

hi,
you may have more than 2 items in one cell?

Yes please. If possible I would want to sum the returned values into a single cell.

try this for 2 values in a cell:
=IFERROR(XLOOKUP(LEFT(F2,SEARCH(",",F2)-1),$A$2:$A$11,$B$2:$B$11)+XLOOKUP(MID(F2,SEARCH(",",F2)+1,99),$A$2:$A$11,$B$2:$B$11),XLOOKUP(F2,$A$2:$A$11,$B$2:$B$11))

Originally Posted by belinda200

try this for 2 values in a cell:
=IFERROR(XLOOKUP(LEFT(F2,SEARCH(",",F2)-1),$A$2:$A$11,$B$2:$B$11)+XLOOKUP(MID(F2,SEARCH(",",F2)+1,99),$A$2:$A$11,$B$2:$B$11),XLOOKUP(F2,$A$2:$A$11,$B$2:$B$11))

Thanks this worked. But it does not cater for duplicate values. for example, if I have duplicate product id: MK003 in the column it does not sum them.
Also it does not work if the comma separated values are more than two.

No need for XLOOKUP

Cell G2 formula , Drag down

Formula:

Please Login or Register  to view this content.