error (bug?) with VLOOKUP function

[Luc]

Hallo,

I have a problem with VLOOKUP function (excel 2007). I tried what follows
several times and i always get the same error/

considere table (B5:C9) sorted according the first column:

name salary
baba 400
bibi 600
bobo 200
bubu 300

cel B13 contains: bibi
cel B14 contains: baba

in D13: =VLOOKUP(B13;B$5:C$9;2) and i drag it to D14

Result:
in D13: 600 (this is correct)
in D14: #N/B

Each time, with the first name only of any sorted table, i get that error.
Any explanation?
Thanks

Luc

 

[Niek Otten]

see answer in other newsgroup

 

[Bob Umlas]

B5:C9 is NOT sorted - "name" comes first! If you change your reference to
B$6:C$9 it works fine. Or if you change it to find an exact match:
=VLOOKUP(B13;B$5:C$9;2;FALSE) it will also work fine.

 

[Luc]

But "name" is the fieldname.
If you click in the first column of the table and then rightclick and take
"Sorted from A to Z"), then is the table sorted according the first column.
But of course, the first row containing the fieldnames is not sorted.

Strange that excel recognises that B5:C9 is a table when sorting it (because
it doesn't take the first row for sorting), but not in the function VLOOKUP.

 

[Peo Sjoblom]

The easy solution would be to exclude the headers from the lookup table.

When you sort you can set in the sort dialogue box if the table has headers
or not, you cannot do that when using the formula.

--


Regards,


Peo Sjoblom

 

[Ron Rosenfeld]

Strange that excel recognises that B5:C9 is a table when sorting it (because
it doesn't take the first row for sorting), but not in the function VLOOKUP.

When you are doing a sort, Excel makes a guess as to whether headers are
present or not, which you can override.

In entering a function, there is no argument to indicate whether your table has
headers or not. I suppose such an argument could have been added, but the
designers apparently chose not to add that complexity to the function, but
rather assumed that the user could determine that when entering the TABLE
range.
--ron

 

[Luc]

and with the db-functions like DBMAX etc ... the whole table (with fieldrow)
must be selected ...
Why here and not with VLOOKUP?

 

[Ron Rosenfeld]

and with the db-functions like DBMAX etc ... the whole table (with fieldrow)
must be selected ...
Why here and not with VLOOKUP?

You'd have to ask the designers. You can use VLOOKUP in a table that has no
field names.
--ron

 

[Peo Sjoblom]

The DB functions are relics just to make Excel compatible when Lotus was the
main spreadsheet player. Besides it's not a good comparison since it is
easier to remove the headers from the range in the vlookup formula than
adding all the extra that a DB function demands like a criteria range, a
field name plus some very less than logical ways of setting up the criteria
range.

--


Regards,


Peo Sjoblom