Autofill Column Index in Vlookup

[Carin]

I would like to autofill a vlookup. What I'm trying to get is the column index number to increase by 1.

Example:

B1
=VLOOKUP($A$1,'Anchorage AK'!$A$1:$W$26,4,FALSE)

C1
=VLOOKUP($A$1,'Anchorage AK'!$A$1:$W$26,5,FALSE)

D1
=VLOOKUP($A$1,'Anchorage AK'!$A$1:$W$26,6,FALSE)

When I drag the cell, the column index stays at 4.

Thanks

 

[RoryA]

You can use INDEX and match instead:
in B1: =INDEX('Anchorage AK'!D$1:D$26,MATCH($A$1,'Anchorage AK'!$A$1:$A$26,0))
and copy across.
HTH

 

[sous2817]

You could also use a helper row.


Put 4 in Z1, 5 in Z2, and 6 in Z3

Vlookup($A$1,'Anchorage AK'!$A$1:$W$26,Z1,FALSE) and copy across.

 

[RoryA]

PS It would be even more efficient if you stored the MATCH part in one column and used it repeatedly:
B1: =MATCH($A$1,'Anchorage AK'!$A$1:$A$26,0)
C1: =INDEX('Anchorage AK'!D$1:D$26,$B1)
then copy C1 across as far as required. That way you only have to look A1 up once.

 

[Carin]

I'm lost...did you make up cell references. I've never used Index before. It's telling me I have multiple arguments listed..???

 

[Carin]

Okay...I get it now.

I'll try both examples out.

Thanks

 

[RoryA]

Nope, I used the references you gave. Which version of Excel?
Incidentally, to use VLOOKUP, you could use:
B1: =VLOOKUP($A$1,'Anchorage AK'!$A$1:$W$26,COLUMN()+2,FALSE)
and copy across.

 

[VoG]

Try

=VLOOKUP($A$1,'Anchorage AK'!$A$1:$W$26,COLUMN()+2,FALSE)

Please note that this will go haywire if you subsequently add columns to the left of B.

 

[Papa Omaha]

I like to embed a MATCH in the column index number if the items share the same header. I find it avoids headaches, especially if you have a HUGE spreadsheet you're dealing with.

something like this:
=VLOOKUP($A$2,'Anchorage AK'!$A$1:$W$26,MATCH(B$1,'Other Sheet'$B$1:$Z$1,0),FALSE)