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

[Gene]

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?

[RagDyer]

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" <Ge...@discussions.microsoft.com> wrote in message
news:225C9F35-E4E1-4050...@microsoft.com...

[bnc...@gmail.com]

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.