Using Like in Excel IF Statement

Super Contributor

Using Like in Excel IF Statement

Hello Experts,

I need to adjust the below to check if A11 and FBL1N_Pmts[[#All],[Vendor]] are "Like".  A11 and FBL1N_Pmts[[#All],[Vendor]] are Company Names and sometimes they are spelled a little differently and I think by using Like it will account for those differences.

I know CountIF is the solution but I cant figure out where to place the CountIF.

Grateful for the help.  thank you.

=IF(ISNA(VLOOKUP(A11&B11,FBL1N_Pmts[[#All],[Vendor]]&FBL1N_Pmts[[#All],[Reference]],1,FALSE)),"NOT IN FBL1N","IS IN FBL1N")

7 Replies

Re: Using Like in Excel IF Statement

not sure what you mean by "Like" but if you mean you want to be able to use wildcard characters like "*" then consider using XLOOKUP (assuming you have Excel 365. it will also handle if none are found:
=IF(XLOOKUP("*"&A11&"*"&B11,FBL1N_Pmts[[#All],[Vendor]]&FBL1N_Pmts[[#All],[Reference]],FBL1N_Pmts[[#All],[Vendor]],FALSE,2),"NOT IN FBL1N","IS IN FBL1N")
or you could use FILTER

Re: Using Like in Excel IF Statement

Hi, thanks for the response. I think that you are correct with your explanation. From what I have read online, countif seems to be the way to handle "Like" but my knowledge is minimal.

The formula results with XLOOKUP above seems to not return the expected results. It returns the false value each time. I changed the data to see if the result would change and the result didnt change.

I am not too familiar with the formula. Let me know if you have another suggestion. thank you

Re: Using Like in Excel IF Statement

Wildcards are useful if you want to match part of a string, e.g. "begins with" or "contains", but they won't help with variations in spelling. You might need the Fuzzy Lookup Add-In for Excel

Re: Using Like in Excel IF Statement

thank you Hans.
I have installed it and it looks like I have to join tables to use the fuzzy lookup. I will explore this more. In the meantime, does Countif work well to capture misspellings as too?

Re: Using Like in Excel IF Statement

If the result of COUNTIF is not what you'd expect, that might be caused by a misspelling, but it won't tell you where...

Re: Using Like in Excel IF Statement

Hans, I have not tried COUNTIF yet. I tried but have not had success in modifying the formula. Grateful for your help.

Re: Using Like in Excel IF Statement

I might be confused in the usage of COUNTIF with "*" for misspellings. Maybe this is not a solution.