How can I automatically update column index number in VLookup whe.
 

If I insert a column in the range of a VLOOKUP Command, the range adjusts,
but the column index number does not.

Is there a work around for this?

Use Column() in place of the index number.

For instance:

=VLOOKUP(E1,A1:D10,3,0)

Can be revised to:

=VLOOKUP(E1,A1:D10,COLUMN(C1),0)

Where Column(C1) represents the *third* column,
*NOT* Column C

For example:

=VLOOKUP(V1,W1:Z10,3,0)

Can be revised to:

=VLOOKUP(V1,W1:Z10,COLUMN(C1),0)

BUT, in this case, if you inserted a column between W and Z,
column C would *still* be column C, and therefore , *still* equate to 3 !

So, you'd have to try something like this:

=VLOOKUP(V1,W1:Z10,COLUMN(Y1)-22,0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Gene" wrote in message
...
If I insert a column in the range of a VLOOKUP Command, the range adjusts,
but the column index number does not.

Is there a work around for this?

How can I automatically update column index number in VLookup whe.
 

A B C D E
Tom 1 All Number
Tom 1
Lucy 2
Jessy 3
Bill 4
Singer 5

In cell B2, type: =VLOOKUP(A1,D:E,COLUMNS(D1:E1),0)

Now you can do whatever you want to adjust.