VLOOKUP Oddity

[SamuelT]

Hi all,

I've got a spreadsheet that's using VLOOKUP. For some reason, there are
a number of rows that the function does not pick up; however, for the
majority it is working fine.

Can anyone suggest reasons for this oddity?

TIA,

SamuelT

 

[Niek Otten]

Probably your few rows (or the other ones!) are text, although they may look
like numbers and are formatted as numbers. You can easily check with the
ISTEXT() function.
Remedy: Format an empty cell as number. Enter the number 1. Edit>Copy.
Select your "text-numbers". Edit>Paste special, check Multiply.

 

[SamuelT]

It's OK - sorted it myself.

 

[SamuelT]

Nah - it wasn't that, although it was a text problem.

I have two identical lists. However, one of the lists hadn't had a
small change made to them, and hence the VLOOKUP could find it. Duh!

Thanks for the advice anyhow!

SamuelT

 

[L. Howard Kittle]

Hi SamuelT,

Perhaps your range is not set to absolute, as in the second formula, when
you filled down?

=VLOOKUP(A1,B1:C10,2,0)

=VLOOKUP(A1,$B$1:$C$10,2,0)

HTH
Regards,
Howard