Forum Discussion
Tony2021
May 08, 2022Steel 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
Sort By
- mtarlerSilver Contributornot 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- Tony2021Steel ContributorHi, 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