Vlookup - Identify Label - Excel Programming Bug

[andy_suffers_Excel_2003_over-engineering]

Hi. I use VLOOKUP formula's every single day - I use them A LOT !
My current machine has Ms Excel 2003.

A normal Vlookup formula looks like :

=VLOOKUP(A2,Sheet1!$A$16:$B$21,16,FALSE)

Prior to Ms Excel 2003, I have NEVER had a problem with Vlookup formula's.

HOWEVER.....Some "Genius" Programmer at Excel has bugged the Worksheet
command.

Now, when I go to type "FALSE" at the end of a Vlookup command, Excel
suddenly opens a Dialog Box looking to "Identify Label"....

If I try to close this box or cancel it - Excel crashes with the loss of all
unsaved work.

There does not seem to be any means of turning this damned function off
either.

It seems to me a classic case of somebody trying to 'Fix' Excel when it's
not broken.


PLEASE PLEASE....If there's any download, bug fix or <ANYTHING> I can do to
stop this damned dialog box opening up and crashing Excel - I would LOVE to
know what it is.

Thanks,
A.

 

[kassie]

I do not have 2003, but I notice people using an "0", iso FALSE. Maybe try
and replace your FALSE with a zero?

 

[Roger Govier]

Hi Andy

Don't think its a bug!!!
Your data range is 2 columns wide A:B and you are trying to use an
offset of 16!!

--
Regards

Roger Govier


"andy_suffers_Excel_2003_over-engineering"

 

[Pete_UK]

I don't have XL2003, so I haven't come across this, but I do find all
the green triangles and "helpful" features to be a bind on the
occasions when I use this version on others' machines.

However, the formula you posted is trying to get data from column 16 of
a range which is only 2 columns wide. I appreciate that you might have
just typed the formula directly into your post so you weren't too
concerned about the syntax, but if this is what you had in your
worksheet then maybe one of Excel's "helpful" features discovered your
error and was trying to alert you to it (using a message which means
nothing to anyone except the programmer who dreamt it up!).

Hope this helps.

Pete

 

[kassie]

Oops, missed that one! You are so very right and correct!

 

[Gord Dibben]

I would just go to Tools>Options>Calculation and uncheck "Accept labels in
formulas"

Does that rectify the problem?

If so, check out and see where you may have conflicting ranges with label names.

This KB article for excel 97 gives this bit of info. Could be it also applies
to newer vesrions but article not updated.

http://support.microsoft.com/kb/161881/en-us

If multiple tables on the same worksheet contain identical labels, the table
Microsoft Excel uses is determined by the location of the cell that contains the
formula. In general, the table that is referenced is to the left and/or above
the cell that contains the formula.

For example, if a worksheet contains a table in cells A1:E10 and a table in
cells A15:E25, if you type a formula in cell A30, Microsoft Excel uses the
labels in the table in cells A15:E25. However, if you type the same formula in
cell A12, Microsoft Excel uses the first table in cells A1:E10. If Microsoft
Excel is unable to determine to which table you are referring, it displays the
"Identify Label" dialog box. This dialog box prompts you to select the cell that
contains the label you want to use.


Gord Dibben MS Excel MVP