VLOOKUPS and Special Characters...

[scottleger4]

I'm trying to lookup certain metrics using unique location designators, and a few are giving me headaches because the characters seem to be acting like wildcards and are returning the wrong information. Here is what I have...

Attempting to lookup "36*" or "32Y?" are giving me a bit of a hardtime. I have come across a couple other posts on other sites suggesting INDEX MATCH MATCH or SUBSTITUTE functions, but I cant seem to get those to work either. Thanks in advance for your help!

 

[Fluff]

What version of excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 or 2021 you could use xlookup or xmatch

 

[scottleger4]

What version of excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 or 2021 you could use xlookup or xmatch

Account Details updated. I'm using Windows Office 365.

As I understand it, the "*" and "?" characters are excel designated wildcards...wouldn't xlookup and xmatch run into the same issues I'm having with vlookup? Or perhaps I just have have the syntax right...

 

[Fluff]

wouldn't xlookup and xmatch run into the same issues I'm having with vlookup?

Nope, they both treat the text as text unless you specify you want a wildcard match.