How to use Excel VLOOKUP function with words that begin with the letters AB

Question

For title naming, I have a list of words that I employ. As I come up with new names, I want to make sure I'm not using words that have already been used. When I encountered terms that start with the letters AB (such as absolute, abolished, etc.) that I had never used before, my Excel formula appeared to be operating as intended. 

=IF(VLOOKUP(B48,Used!A:A,1)=B48,"USED","")

New Word	Result	Formula Commentary
Pretend		...VLOOKUP(B48,Used!A:A,1)=B48 correctly returns FALSE because it did not find the word
Abundant	USED	...VLOOKUP(B48,Used!A:A,1)=B48 correctly returns TRUE because it did find the word
Absolute	#N/A	...VLOOKUP(B48,Used!A:A,1)=B48 returns #N/A when it does not find any word that begins with AB

I've tried using the TEXT function in the formula to format B48, but no change. I've tried using Range_Lookup in the VLOOKUP syntax, but no change.

Any help would be appreciated.

Answer 1

If you lookup "AB" in Excel, it does not search for a value that starts with it, but for a value that equals it. If you want to lookup any word starting with "AB" you have to tell Excel by adding a wildcard: =VLOOKUP(B48&"*",Used!A:A,1,0)

I hope this helps you.