0

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.

T-RexMan
  • 13
  • 4

1 Answers1

1

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

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • OP is looking to look for comma-delimited values and return the results as a comma-delimited value too. – JvdV Jan 03 '22 at 09:39
  • 1
    Updated answer. – Harun24hr Jan 03 '22 at 10:20
  • @Harun24HR Thx! What are the "" and why does this function not work when there is a '&' sign in the lookup table names? – T-RexMan Jan 04 '22 at 06:17
  • @T-RexMan I would recommend to read this [article](https://stackoverflow.com/a/61837697/5514747) for more details and clarification. – Harun24hr Jan 04 '22 at 06:20
  • @Harun24HR Thanks! So What should I do lets say if one of my chipsets was called "A14&Bionic" instead? – T-RexMan Jan 04 '22 at 06:40
  • @T-RexMan Then we need few extra function to substitute `&` and after getting result, reform `&` to result. That would be long formula. – Harun24hr Jan 04 '22 at 08:08
  • Just a FYI, but `FILTERXML()` will return the ampersand back in it's original form. No need to use multiple `SUBSTITUTE()` functions. Just one will =) – JvdV Jan 05 '22 at 11:22