Forum Discussion

Matt_Paz's avatar
Matt_Paz
Copper Contributor
Feb 20, 2025

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...
excel
Formulas and Functions
Matt_Paz's avatar
Matt_Paz
Copper Contributor
Feb 20, 2025

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

Patrick2788's avatar
Patrick2788
Silver Contributor
Feb 20, 2025

Then you can use the wildcard version (it's a bit longer):

=XLOOKUP("*"&name&"*",names,team,"",2)

 

  • Matt_Paz's avatar
    Matt_Paz
    Copper Contributor
    Feb 21, 2025

    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

    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Feb 21, 2025

      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!

Resources