CODEWITHSUNDEEP
excelvlookuplookupxlookup
T-RexMan
T-RexMan

Reputation: 13

Excel lookup function to look for multi word cell for multi result

I was looking at this post: https://www.exceldemy.com/vlookup-if-cell-contains-a-word-within-text-in-excel/ and wondered is it possible to do something like put "A14, Snapdragon" as the lookup word in one cell and the function would reply list of all possible lookup results "iPhone 12, Xiaomi Mi 11 Pro" also in one cell.

Upvotes: 0

Views: 157

Answers (1)

Harun24hr
Harun24hr

Reputation: 37125

If you have EXCEL-365 then give a try on below formula.

=TEXTJOIN(", ",TRUE,XLOOKUP("*"&FILTERXML("<t><s>"&SUBSTITUTE(A14,",","</s><s>")&"</s></t>","//s")&"*",A2:A9,B2:B9,,2))

enter image description here

Upvotes: 1

Related Questions