VLOOKUP and wildcards

[Guest]

Having an issue with this...any help would be appreciated

I am using VLOOKUP to return values from another worksheet and it works as intended 99% of the time
BUT...
some of the codes that I am trying to find the values for contain an asterisk. I do not have control over the codes or I would have changed the offending codes to not include the asterisk. In this case, I am trying to lookup the values for the following code: G*. I have the arguments within the vlookup function set to FALSE. (which according to Microsoft, should only return values if there is an EXACT match) This is NOT working, as the function returns the first value that begins with G. How do I get VLOOKUP to find G* and G* ONLY. I can handle the N/A errors that result when that value is found. Here is the formula for your reference: =VLOOKUP($A139,Data!$A$3:$N$427,2,FALSE) --- the value in A139 is G*. Going nutz! Thanks In Advance!

 

[Frank Kabel]

Hi
put the following search criteria in A139:
G~*

As the '+' is used as wildcard you have to precede it with an '~'

 

[Guest]

Frank..YOU ROCK! Thanks for the quick response...BTW ...that worked

----- Frank Kabel wrote: ----

H
put the following search criteria in A139
G~

As the '+' is used as wildcard you have to precede it with an '~


-
Regard
Frank Kabe
Frankfurt, German


Comcaster wrote

 

[Dave Peterson]

=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~","~~"),"?","~?"),"*","~*"),
Sheet2!$A:$B,2,FALSE)

Is one way to handle both the wild cards and escape character.