VLOOKUP Conversion from Excel 2003 to Excel 2007

  • Thread starter Thread starter Michael D. Ober
  • Start date Start date
M

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.
 
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.
Click to expand...

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
 
TheHeatons said:
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
Click to expand...

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.
 
Back
Top