Vlookup; dynamic column index

[AK]

Is there a way to create a vlookup equation with a dynamic column index? I'd
like to drag the equations to the right and have the column index refer to
the next i.e. 2 goes to 3 goes to 4 and so on...


thanks in advance

 

[Peo Sjoblom]

Try

=VLOOKUP($A$1,Sheet2!$A$2:$F$200,COLUMN(B:B),0)

would equal

=VLOOKUP($A$1,Sheet2!$A$2:$F$200,2,0)


will increment the index starting with 2 (column B is 2)
and copied across, note the absolute reference regarding the lookup value
and lookup table or else they will increment as well

Regards,

Peo Sjoblom

 

[RagDyer]

Just to add to Peo's explanation, if your data list was from
D1:J100, and you wanted to start returning your values from Column E, you
would *still* use Column B in your initial formula.

=VLOOKUP($A$1,$D$1:$J$100,COLUMN(B1),0)

Since Column E is the *second* column of the data list, and XL *only* sees
Column(B1) as the number 2 in this context.
--

HTH,

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


Try

=VLOOKUP($A$1,Sheet2!$A$2:$F$200,COLUMN(B:B),0)

would equal

=VLOOKUP($A$1,Sheet2!$A$2:$F$200,2,0)


will increment the index starting with 2 (column B is 2)
and copied across, note the absolute reference regarding the lookup value
and lookup table or else they will increment as well

Regards,

Peo Sjoblom