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]

Nice catch, jonmo.

 

[Jonmo1]

Nice catch, jonmo.

Thanks,

I noticed it because my first thought was that the blank cells were not really blank...

So I did =ISBLANK(A1)

Which returned 1 (not true or false).
Symptom of lotus issues..

 

[Jonmo1]

Glad to help, thanks for the feedback..

These options are there for compatibility issues with Lotus.
And they can be usefull, but also cause very strange behavior in the strangest places.
As you have seen here.

How/When/Why it got checked....you're guess is as good as mine..

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 ?

 

[Dude]

Is there a reason that it doesn't affect Excel 2003 even though "Transition formula evaluation" is checked ?
Shouldn't they both malfunction similarly?

 

[Jonmo1]

I see the same strange results if opened in 2003 or 2007

 

[Dude]

I have been working with these sheets for years. Never experience this problem in Office XP or Office 2003 otherwise I would not have been able to use them from the start. It was only yesterday that I came across this issue.


Is there any way to strip out any hint of Lotus in the workbook so that the check box isn't checked automatically when the file is opened ?

 

[Jonmo1]

You can use Brute force to just make sure it's turned off when you open the book...

You could put this in the Open Event

Dim ws As Worksheet
For Each ws In Worksheets
    ws.TransitionExpEval = False
Next ws

 

[Dude]

I agree I could put that worksheet open event code in there but with excel 2007 most people just disable any code when opening the workbook.
I didn't have this issue until the file was shared and others began to use it (which will be happening more often) as I don't have 2007 on my desktop I wasn't aware of this issue.
If others use it I will either have to put them in a trusted location or have them turn off their macro security.

Perhaps I should just start fresh and recreate the workbook in native Excel 2007 and save as 2003.

Interesting issue though.