excelif-statementexcel-formulavlookupxlookup

Excel IF(ISNUMBER(SEARCH ignore certain characters

Ultimately I'm trying to use a formula to see if a string in one cell has a partial match to what is in another cell while ignoring wildcards in between. . The formula I'm using in B2 to perform a partial search/match is

=IF(ISNUMBER(SEARCH(Q2,J2)),"Matches","Review Req")

I'm wondering if it can ignore certain characters or use wildcards of some sort. Because as you can see it is not an exact match between

SE Ext. 22 and SE Ext '22 or SE Ext 22

OE 23 and OE '23 or OE23 or OE 23

SE 23 and SE '23 or SE23 or SE 23

enter image description here

and my limitation is formula ONLY is a very strong preference. Does anyone have a alternative to trying to do what I'm trying?

After getting help from below I got as far as this

So I tried to substitute and I got the result desired

=IF(ISNUMBER(SEARCH(Q2,(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2,"SE Ext '22","SE Ext. 22"),"SE Ext 22","SE Ext. 22"),"OE '23","OE 23"),"OE23","OE 23"),"_60K",""),"SE '23","SE 23"),"SE23","SE 23")))),"Matches","Review Req")

Solution

  • You can use SUBSTITUTE to change "unwanted" characters in both strings and the do SEARCH (warning: this might cause false positives in some cases):

    To SUBSTITUTE first column:

    =REDUCE(A1:A4,{"'"," ","."},LAMBDA(a,b,SUBSTITUTE(a,b,"")))

    To SUBSTITUTE second column (just change range):

    =REDUCE(B1:B4,{"'"," ","."},LAMBDA(a,b,SUBSTITUTE(a,b,"")))

    Combine it:

    =IF(MAP(REDUCE(A1:A4,{"'"," ","."},LAMBDA(a,b,SUBSTITUTE(a,b,""))),REDUCE(B1:B4,{"'"," ","."},LAMBDA(a,b,SUBSTITUTE(a,b,""))),LAMBDA(z,x,ISNUMBER(SEARCH(x,z)))),"Matches","Review")

    Result:

    enter image description here

    If you want different characters to substitute, just add them to these arrays in formula {"'"," ","."}

    Shortened version to SUBSTITUTE both columns at once:

    =IF(LET(y,REDUCE(A1:B4,{"'"," ","."},LAMBDA(a,b,SUBSTITUTE(a,b,""))),ISNUMBER(SEARCH(INDEX(y,,2),INDEX(y,,1)))),"Matches","Review")

    If your original columns are separated by other columns, you can HSTACK them HSTACK(A1:A4,B1:B4) and use instead of range A1:B4