Reputation: 481
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
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
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