Searching a cell value against a column of values

Question

Hi allHope you are well.I have a table as follows:I am after a formula to search the whole of column I for the names in column A. If found, return the Team name in column H.How is this possible? LET?Thanks for any helpMatt

patrick2788 · Answer

You can do this with XLOOKUP with match mode set for regex:=XLOOKUP(name,names,team,"",3)&nbsp;

matt_paz · Answer

Hi, looks like my version doesnt allow option 3 in XLOOKUP - I get the #VALUE error

patrick2788 · Answer

Then you can use the wildcard version (it's a bit longer):=XLOOKUP("*"&amp;name&amp;"*",names,team,"",2)&nbsp;

matt_paz · Answer

Hi Patrick2788&nbsp;Thanks so much. This works but if my source cell is more "complicated" XLOOKUP fails.For example:The XLOOKUP fails in all cases

patrick2788 · Answer

Then you'd need to split the terms with TEXTSPLIT, add the wildcards, and then check them against the ingredients. This is when the adventure begins!

Forum Discussion

Searching a cell value against a column of values

5 Replies

Resources