Forum Discussion
Searching a cell value against a column of values
Hi all Hope 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 thi...
You can do this with XLOOKUP with match mode set for regex:
=XLOOKUP(name,names,team,"",3)
Hi, looks like my version doesnt allow option 3 in XLOOKUP - I get the #VALUE error
Then you can use the wildcard version (it's a bit longer):
=XLOOKUP("*"&name&"*",names,team,"",2)Hi Patrick2788
Thanks so much. This works but if my source cell is more "complicated" XLOOKUP fails.
For example:
The XLOOKUP fails in all cases
