Is this a Bug in excel 2007 ?

[Dude]

I have a named table that is, for example, 2 columns by 10 rows.
I have, in a cell, on the same sheet or otherwise, a vlookup formula that references the table and range_lookup = TRUE. The left most column is sorted ascending.
The proper behavior should be that the formula searches for an exact match to the lookup_value OR the nearest lower value if an exact match is not found.

The problem/bug;
If there isnt 10 rows of values in the 10 row table AND the lookup_value is greater than the highest value, the formula evaluates to zero. Not #N/A or #VALUE or any other error, just 0.
This formula and methodology has been working fine in previous versions (XP, 2003 ) but 2007 seems not to be able to handle these conditions properly.

Is there any setting or fix to force 2007 to function properly as previous versions do with vlookup?

 

[shg]

That's not the behavior I see.

       A- -B-- C -D-- -------------E-------------
   1    1 abel                                   
   2    2 bill                                   
   3    3 cain   ivan D3: =VLOOKUP(11, A1:B10, 2)
   4    4 drew                                   
   5    6 eric                                   
   6    7 fred                                   
   7    8 gary                                   
   8    9 hank                                   
   9   10 ivan                                   
  10      john

Got an example?

 

[Dude]

I have workbooks that are created in excel 2003. When opened in excel 2007 the vlookup function does not work correctly. It seems to be only in these workbooks that this happens.

I have tried to recreate the issue in a native 2007 workbook and cannot.
I have a cost estimating sheet that I use and I have many of them and in each of them, when opened in 2007, vlookup exhibits this behavior.
I have tried to recreate this issue in other types (other than my estimating sheet) of 2003 workbooks and cannot.

I am perplexed.

 

[tusharm]

Can you share the specific formula you are using and the conditions when the result is incorrect?

I have a named table that is, for example, 2 columns by 10 rows.
I have, in a cell, on the same sheet or otherwise, a vlookup formula that references the table and range_lookup = TRUE. The left most column is sorted ascending.
The proper behavior should be that the formula searches for an exact match to the lookup_value OR the nearest lower value if an exact match is not found.

The problem/bug;
If there isnt 10 rows of values in the 10 row table AND the lookup_value is greater than the highest value, the formula evaluates to zero. Not #N/A or #VALUE or any other error, just 0.
This formula and methodology has been working fine in previous versions (XP, 2003 ) but 2007 seems not to be able to handle these conditions properly.

Is there any setting or fix to force 2007 to function properly as previous versions do with vlookup?

 

[Dude]

I can send an example of the workbook so that you may see the behavior yourself if you would like.

 

[shg]

You could put it on box.net and post a link.

 

[Jonmo1]

I would venture a guess that the problem is not with vlookup, but it's here

I have a named table that is, for example, 2 columns by 10 rows.

1. Does the vlookup work if you hard code the formula with the range like A1:B10 ?
2. Is that a Table, or a Named range from Insert - Name - Define?

 

[Dude]

it is a named range not a table.
It makes no difference if it is defined like B2:D12 or named they are essentialy the same. It happens either way.

File can be found here.
http://www.box.net/shared/aa77n0gjtggxl4fygp6v

Make a list and place a vlookup formula to evaluate the list, try to lookup a value greater than the largest value in the list and be sure to define your lookup range larger than the number of rows in the list.

In 2003 I have predefined table sizes but sometimes I don't have the maximum amount of data. Sometimes my list is only 2 rows sometimes 8 rows but the logic works in 2003 but not 2007 (In these workbooks).

 

[Jonmo1]

Go to Excel Options - Advanced
Scroll to bottom, under "Lotus Compatibility Settings"
UNcheck "Transition formula evaluation"


Hope that helps.

 

[Dude]

That's the ticket !

Simple little check box has been driving me crazy.

Thank you very very much kind sir.

Any idea why this happens or what Lotus has to do with it ?