Excel - How to lookup multiple values in comma separated range

Question

I have this comma separated values in column A in and corresponding values in column B.

I want to lookup values of column E and return the values in column F where duplicate values return the next corresponding value of the same match.

I have tried this formulae but did not get the desired results

    =INDEX($B$2:$B$13, SMALL(IF("*"&E2&"*"=$A$2:$A$13, 
ROW($A$2:$A$16116)-MIN(ROW($A$2:$A$13))+1, 0), 
COUNTIF(E2:$E$2, E2)))

Please anyone here to help me?

Mayukh Bhattacharya · Accepted Answer · 2022-03-07 19:50:35Z

1

Is this what you are looking for, there may be a much more elegant & eloquent way to solve however even this works as well,

Formula used in cell E2

    =IFERROR(INDEX($B$2:$B$13,AGGREGATE(15,6,
(ROW($B$2:$B$13)-1)/ISNUMBER(SEARCH(", "&D2&", ",", "&$A$2:$A$13&", ")),
COUNTIF($D$2:D2,D2))),"")

answered Mar 7, 2022 at 19:50

29.2k9 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jos Woolley · Accepted Answer · 2022-03-07 20:27:42Z

0

In F2:

=INDEX(FILTER(B$2:B$13,ISNUMBER(FIND(","&E2&",",","&SUBSTITUTE(A$2:A$13," ","")&","))),COUNTIF(E$2:E2,E2))

and copied down.

answered Mar 7, 2022 at 19:53

Jos Woolley

9,1322 gold badges7 silver badges12 bronze badges