Using Like in Excel IF Statement

%3CLINGO-SUB%20id%3D%22lingo-sub-3340561%22%20slang%3D%22en-US%22%3EUsing%20Like%20in%20Excel%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3340561%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Experts%2C%3C%2FP%3E%3CP%3EI%20need%20to%20adjust%20the%20below%20to%20check%20if%20A11%20and%26nbsp%3BFBL1N_Pmts%5B%5B%23All%5D%2C%5BVendor%5D%5D%20are%20%22Like%22.%26nbsp%3B%20A11%20and%26nbsp%3BFBL1N_Pmts%5B%5B%23All%5D%2C%5BVendor%5D%5D%20are%20Company%20Names%20and%20sometimes%20they%20are%20spelled%20a%20little%20differently%20and%20I%20think%20by%20using%20Like%20it%20will%20account%20for%20those%20differences.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20CountIF%20is%20the%20solution%20but%20I%20cant%20figure%20out%20where%20to%20place%20the%20CountIF.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EGrateful%20for%20the%20help.%26nbsp%3B%20thank%20you.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ISNA(VLOOKUP(%3CSTRONG%3E%3CFONT%20color%3D%22%23DF0000%22%3EA11%3C%2FFONT%3E%3C%2FSTRONG%3E%26amp%3BB11%2C%3CSTRONG%3E%3CFONT%20color%3D%22%23DF0000%22%3EFBL1N_Pmts%5B%5B%23All%5D%2C%5BVendor%5D%5D%3C%2FFONT%3E%3C%2FSTRONG%3E%26amp%3BFBL1N_Pmts%5B%5B%23All%5D%2C%5BReference%5D%5D%2C1%2CFALSE))%2C%22NOT%20IN%20FBL1N%22%2C%22IS%20IN%20FBL1N%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3340561%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3340618%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Like%20in%20Excel%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3340618%22%20slang%3D%22en-US%22%3Enot%20sure%20what%20you%20mean%20by%20%22Like%22%20but%20if%20you%20mean%20you%20want%20to%20be%20able%20to%20use%20wildcard%20characters%20like%20%22*%22%20then%20consider%20using%20XLOOKUP%20(assuming%20you%20have%20Excel%20365.%20it%20will%20also%20handle%20if%20none%20are%20found%3A%3CBR%20%2F%3E%3DIF(XLOOKUP(%22*%22%26amp%3BA11%26amp%3B%22*%22%26amp%3BB11%2CFBL1N_Pmts%5B%5B%23All%5D%2C%5BVendor%5D%5D%26amp%3BFBL1N_Pmts%5B%5B%23All%5D%2C%5BReference%5D%5D%2CFBL1N_Pmts%5B%5B%23All%5D%2C%5BVendor%5D%5D%2CFALSE%2C2)%2C%22NOT%20IN%20FBL1N%22%2C%22IS%20IN%20FBL1N%22)%3CBR%20%2F%3Eor%20you%20could%20use%20FILTER%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3340828%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Like%20in%20Excel%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3340828%22%20slang%3D%22en-US%22%3EHi%2C%20thanks%20for%20the%20response.%20I%20think%20that%20you%20are%20correct%20with%20your%20explanation.%20From%20what%20I%20have%20read%20online%2C%20countif%20seems%20to%20be%20the%20way%20to%20handle%20%22Like%22%20but%20my%20knowledge%20is%20minimal.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20formula%20results%20with%20XLOOKUP%20above%20seems%20to%20not%20return%20the%20expected%20results.%20It%20returns%20the%20false%20value%20each%20time.%20I%20changed%20the%20data%20to%20see%20if%20the%20result%20would%20change%20and%20the%20result%20didnt%20change.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20not%20too%20familiar%20with%20the%20formula.%20Let%20me%20know%20if%20you%20have%20another%20suggestion.%20thank%20you%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3342294%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Like%20in%20Excel%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3342294%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWildcards%20are%20useful%20if%20you%20want%20to%20match%20part%20of%20a%20string%2C%20e.g.%20%22begins%20with%22%20or%20%22contains%22%2C%20but%20they%20won't%20help%20with%20variations%20in%20spelling.%20You%20might%20need%20the%20%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D15011%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EFuzzy%20Lookup%20Add-In%20for%20Excel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3343046%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Like%20in%20Excel%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3343046%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20Hans.%3CBR%20%2F%3EI%20have%20installed%20it%20and%20it%20looks%20like%20I%20have%20to%20join%20tables%20to%20use%20the%20fuzzy%20lookup.%20I%20will%20explore%20this%20more.%20In%20the%20meantime%2C%20does%20Countif%20work%20well%20to%20capture%20misspellings%20as%20too%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3344209%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Like%20in%20Excel%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3344209%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20the%20result%20of%20COUNTIF%20is%20not%20what%20you'd%20expect%2C%20that%20might%20be%20caused%20by%20a%20misspelling%2C%20but%20it%20won't%20tell%20you%20where...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3344668%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Like%20in%20Excel%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3344668%22%20slang%3D%22en-US%22%3EHans%2C%20I%20have%20not%20tried%20COUNTIF%20yet.%20I%20tried%20but%20have%20not%20had%20success%20in%20modifying%20the%20formula.%20Grateful%20for%20your%20help.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3346350%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Like%20in%20Excel%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3346350%22%20slang%3D%22en-US%22%3EI%20might%20be%20confused%20in%20the%20usage%20of%20COUNTIF%20with%20%22*%22%20for%20misspellings.%20Maybe%20this%20is%20not%20a%20solution.%3C%2FLINGO-BODY%3E
Super Contributor

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
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
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

@Tony2021 

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 

@Hans Vogelaar 

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?

@Tony2021 

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...

Hans, I have not tried COUNTIF yet. I tried but have not had success in modifying the formula. Grateful for your help.
I might be confused in the usage of COUNTIF with "*" for misspellings. Maybe this is not a solution.