VLOOKUP Problem in Excel

[cyberprof]

I'm having some problems with a VLOOKUP table.

Here is my formula:

=VLOOKUP(M8,Tables!$A$2:$B$82,2)

Here is a sample of the Lookup Table

Col A Col B

P8c 4.2
P8d 3.8
P8e 3.4
1aa 11
1ab 10.6
1ac 10.2


P8c, P8d, P8e will return the correct figures,
1aa, 1ab, 1ac will return #N/A

However, if I create a seperate table containing 1aa, 1ab, 1ac, and change the formula to look at this table, the correct data is returned.

I cant understand whats happening. Any help please.

Thanks

J

 

[DanAuber]

I'm think you need an extra parameter after the 2 - I normally use FALSE - thus try:

=VLOOKUP(M8,Tables!$A$2:$B$82,2,FALSE)




Dan Auber

 

[xlbo]

FALSE = EXACT match required
TRUE = near match

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Quoting from the function help, that last argument ( called the "range_lookup" argument ), affects usage this way:

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

Cheers, Glenn.

Alien Corn:
“My snorks got no dweezen.” “How does he froojle?” “Shalznik!”

 

[cyberprof]

Thanks for the advice. I've managed to fix the problem. All I've done is sort the columns into ascending order. It now works fine

 

[pbrodsky]

You should still add the extra FALSE arguement to the VLOOKUP. Otherwise, you will get matches from values between the ones you are looking for. Without the FALSE, VLOOKUP does not require an excat match.

 

[cyberprof]

OK, will do