Excel VLOOKUP function

[reichertc]

Maybe I am imagining things, but I recall older versions of Excel doing something that the 2007 version is not.

When using VLOOKUP function, where the table array is a named range, and the column index number is a number, I am having problems with Excel not properly updating the column index number if I insert a new column in the middle of the named range. I believe that Excel 2002/2003 did this. Is this a bug, or intentional? This was one of the primary reasons I used named ranges as arguments in a function, in case I needed to insert/delete columns.

Or do I need to create an argument that correctly calculates the column index number???

 

[GlennUK]

Your memory is faulty, Excel 2002/2003 did not do this. And neither did any earlier versions of Excel.

If you name the column to be extracted, then this will work:

=VLOOKUP(A2,mytable,COLUMN(extract_col)-COLUMN(mytable)+1,FALSE)

I use INDEX and MATCH combination, so as to not have to write such convoluted formulae.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

I use INDEX and MATCH combination, so as to not have to write such convoluted formulae.

Ditto. I always use named column ranges. I rarely use VLOOKUP.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}