Search code examples
excelexcel-formulamatchvlookup

In Excel, looking for a way to convert MATCH formula into a wildcard match to find if a list of terms exist in multiple phrases

I'm looking for a way to convert a Match formula that I use to routinely to check for matches into a wildcard match for Match. I know there is an MatchType parameter in the MATCH formula but when changing from 0 to 1 it does not produce the desired wildcard match I'm looking for.

For exact matches the formula I use is

=IF(ISNA(MATCH(A2,'Lookup wildcard phrase'!A$2:A$4,0)),"FALSE","TRUE")

  1. In the first tab, I have this data

enter image description here

  1. In the second tab, I have this data

enter image description here

For my needs with Exact Matches, I can use this formula

=IF(ISNA(MATCH(A2,'Lookup wildcard phrase'!A$2:A$4,0)),"FALSE","TRUE") to populate the second column row by row in the first tab.

enter image description here

How would I modify this to compare a wildcard for any term column A for every row from first tab with the Match range for column A in the second tab?

I tried experimenting with what I believe is a wildcard with VLOOKUP formula as well but it did not work either.

=IF(ISERROR(VLOOKUP("*"&A2&"*",'Lookup wildcard phrase'!A$2:A$4,1,FALSE)),"FALSE","TRUE")
Solution

  • According to OP's comment that "magpie" is negative for "pie", this should work:

    =SUM(N(TEXTSPLIT(A2," ")=$A$11:$A$13))>0

    enter image description here

    Or to spill at once:

    =BYROW(A2:A6,LAMBDA(x,SUM(N(TEXTSPLIT(x," ")=$A$11:$A$13))>0))