VLOOKUP returning value when range_lookup false

[Guest]

Hello!
I have "false" as my range_lookup in VLOOKUP. It is still returning a match
to something that is not exact. Am I doing something wrong?
Thanks!

 

[Pete_UK]

Post your formula with a description of when this happens, and we might
be able to help you.

Pete

 

[Guest]

Here is my formula:
=VLOOKUP(A23,MFRPT11.XLS!$A$6:$B$20,2,FALSE)
It is matching the numbers for both of the following values:
*******MIXED ADC
*AUTO**MIXED ADC
even though they are not exactly the same.

 

[Bob Phillips]

It is treating the * as wildcards.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Is there a way to prevent it from viewing it as a wildcard and as a character
with value instead?

 

[Bob Phillips]

Hi Jodie,

Not that formula as far as I can see, but you can get it with this somewhat
more convoluted formula

=INDEX($B$6:$B$20,MIN(IF(SUBSTITUTE(A23,"*","~")=SUBSTITUTE($A$6:$A$20,"*","
~"),ROW($A$6:$A$20)-ROW($A$6)+1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Is there a way to prevent it from viewing it as a wildcard and as a character
with value instead?

 

[Guest]

Thanks so much Bob!
--
Jodie

Hi Jodie,

Not that formula as far as I can see, but you can get it with this somewhat
more convoluted formula

=INDEX($B$6:$B$20,MIN(IF(SUBSTITUTE(A23,"*","~")=SUBSTITUTE($A$6:$A$20,"*","
~"),ROW($A$6:$A$20)-ROW($A$6)+1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)