VLOOKUP Conversion from Excel 2003 to Excel 2007

[Michael D. Ober]

I have a large table where the column A is also the lookup column.
Basically, I'm doing a moving average over column A. In excel 2003, the
following works

=VLOOKUP(A264-3000,A:A,1)

In Excel 2007, I get #VALUE!. How can I use column A as the lookup source
and the result column?

For those of you interested, 3000 represents mileage (oil change
increments).

Thanks,
Mike Ober.

 

[TheHeatons]

I have a large table where the column A is also the lookup column.
Basically, I'm doing a moving average over column A. In excel 2003, the
following works

=VLOOKUP(A264-3000,A:A,1)

In Excel 2007, I get #VALUE!. How can I use column A as the lookup source
and the result column?

For those of you interested, 3000 represents mileage (oil change
increments).

Thanks,
Mike Ober.

Hi,

i've just tried your formula with a smaller range and it worked fine.

With A2:A37 filled with numbers 1 thru 36 '=VLOOKUP(A15-6,A:A,1)'
worked fine... the only time it didnt work was if A15-6 was a negative
number in which case the #N/A error appeared

hth

David

 

[Michael D. Ober]

Hi,

i've just tried your formula with a smaller range and it worked fine.

With A2:A37 filled with numbers 1 thru 36 '=VLOOKUP(A15-6,A:A,1)'
worked fine... the only time it didnt work was if A15-6 was a negative
number in which case the #N/A error appeared

hth

David

OK - there are a couple of subtle differences between Excel 2000, XP, 2003
and Excel 2007 in the VLOOKUP function. First, David's comment about
negative numbers is correct, but that's not the reason that what was causing
the #VALUE error for higher mileages. Prior to Excel 2007, if row 1 was a
non-numeric header row, Excel did the expected thing and ignored it. In
Excel 2007, when you have a header row in row one, you must replace the A:A
with A$2:A$65536 or you will get the #VALUE error. I suspect that MS would
tell us that prior to Excel 2007, the VLOOKUP function was "broken", but it
sure made it simpler to use an entire column to as a lookup table.

Mike.