Forum Discussion
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 d...
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
=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
- 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 youWildcards 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
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?
