CODEWITHSUNDEEP
excelexcel-formulavba
A.P.
A.P.

Reputation: 481

Excel column autofill increment by two steps instead of one

In cell B2, I have the following formula 

=VLOOKUP($A2,Sheet1!A:B,2,0)

Now if I autofill to the right in cell C2, the formula becomes

=VLOOKUP($A2,Sheet1!B:C,2,0)

but I would like it to become

=VLOOKUP($A2,Sheet1!C:D,2,0) i.e. C:D instead of B:C (two step increment)

and so forth with 

=VLOOKUP($A2,Sheet1!E:F,2,0)

in cell D2 and etcetera up to more or less 3000 columns.

How could this be achieved? A VBA macro would be fine, but I'd prefer not to use a macro if possible.

Upvotes: 1

Views: 643

Answers (2)

Chronocidal
Chronocidal

Reputation: 8081

You can use OFFSET for this:

=VLOOKUP($A2, OFFSET(Sheet1!$A:$B, 0, 2*(Column()-2)), 2, FALSE)

This will start with the target range $A:$B, then move it down 0 rows and across 2*(Column()-2) columns. For B/C/D this will be 0/2/4 columns

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

Use the non Volatile INDEX()

=VLOOKUP($A2,INDEX(Sheet1!$A:$HZZ,1,(COLUMN(A:A)-1)*2+1):INDEX(Sheet1!$A:$HZZ,1040000,(COLUMN(A:A)-1)*2+2),2,FALSE)

Upvotes: 2

Related Questions