Re: VLOOKUP and wildcards

Comcaster Wrote:
> 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!

The partial solution is to use the Tilda (~) in your vlookup. Like
so..

VLOOKUP("~"&C2,A2:A4,1,FALSE)

This works when there is an asterisk in the search string, but, doesn't
seem to work when there is "?" mark in the string?!


--
cadcrew
------------------------------------------------------------------------
cadcrew's Profile: http://www.hightechtalks.com/m703
View this thread: http://www.hightechtalks.com/t1409554

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

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

cadcrew wrote:
>
> Comcaster Wrote:
> > 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!
>
> The partial solution is to use the Tilda (~) in your vlookup. Like
> so..
>
> VLOOKUP("~"&C2,A2:A4,1,FALSE)
>
> This works when there is an asterisk in the search string, but, doesn't
> seem to work when there is "?" mark in the string?!
>
> --
> cadcrew
> ------------------------------------------------------------------------
> cadcrew's Profile: http://www.hightechtalks.com/m703
> View this thread: http://www.hightechtalks.com/t1409554

--

Dave Peterson