Vlookup col_index_num

[TKS_Mark]

I am using a vlookup function and the col_index_num is 16. When I'm working
in the table that this lookup refers to, sometimes I have to insert or delete
columns. However, the vlookup doesn't increase the col_index_num to 17 if I
insert a column to the left of the looked up item. Why doesn't vlookup work
the same as every other excel formula reference? If I insert columns or rows
around a cell that is used in a formula somewhere in my workbook, that
formula updates to reference the relocated cell. Can't I make vlookup do the
same thing?

 

[Glyn Elrod]

This might not work the way you want it to, but here is an idea.

If the column headings are periods, e.g., "Jan-07", "Feb-07", etc, you might
be able to reference the number of the column heading.

For example, assume "Feb-07" is in cell D2: The formula "=month(d2)" would
give you a 2.

Now, assume that (in this example) that this column would actually be the
third column in a vlookup formula ",3,". The formula "=month(d2)+1" would
give you a 3.

So your vlookup formula could be =vlookup(xx,$a$1:$b$97,(month(d$2)+1,false).

That would yield column three. So, if you inserted a column, your vlookup
would still be looking at the same cell, but the formula would make sure you
get the correct column number.

Anyway, just something to play with; perhaps you aren't using months, but
using a different column heading; hopefully this will give you an idea or
something to think about.

Or maybe not; hope it helps.

 

[aidan.heritage]

I am using a vlookup function and the col_index_num is 16. When I'm working
in the table that this lookup refers to, sometimes I have to insert or delete
columns. However, the vlookup doesn't increase the col_index_num to 17 if I
insert a column to the left of the looked up item. Why doesn't vlookup work
the same as every other excel formula reference? If I insert columns or rows
around a cell that is used in a formula somewhere in my workbook, that
formula updates to reference the relocated cell. Can't I make vlookup do the
same thing?

If you TYPE the value 17 into the formula, then it won't change
(although the RANGE you are looking at will alter). You could use the
COLUMN function to get 17 by referencing a column within your data
range - this (being a cell reference) WOULD update if required.