# v lookup formula autofil

#### bartoni

##### Active Member
Is there a way to drag the vlookup formuls over cells so that the index cell changes. i.e

vlookup(A3, data, 1) i want to drag the formula to the next cell using autofill so that it changes to: vlookup(A3, data, 2). If i try and autofill, it changes A3 to A4 rather than the 1 to a 2.

Thanks

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### fairwinds

##### MrExcel MVP
Hi,

To make the A3 not change, make it absolute i.e. \$A\$3

To make the 1 change, you could refere to column or row depending on how you drag.

e.g. COLUMN()-10

#### bartoni

##### Active Member
Would it be possible for you to explain in more detail what the columns part means or write the formula for me that i would have to begin with and the one i sholud get when i autofill.

Many thanks

#### Brian from Maui

##### MrExcel MVP
Depending on how many Vlookup's you're using and how many columns you're trying to return

=VLOOKUP(\$A\$3,DATA,A\$1,0)

Where A1:F1 is numbered 1,2,3,etc
to avoid Column()

#### Todd Bardoni

##### Well-known Member
Brian from Maui said:
Depending on how many Vlookup's you're using and how many columns you're trying to return

=VLOOKUP(\$A\$3,DATA,A\$1,0)

Where A1:F1 is numbered 1,2,3,etc
to avoid Column()

Man, Brian, that's a good trick! I've always used COLUMN().

Thanks buddy!

#### Brian from Maui

##### MrExcel MVP
tbardoni said:
Brian from Maui said:
Depending on how many Vlookup's you're using and how many columns you're trying to return

=VLOOKUP(\$A\$3,DATA,A\$1,0)

Where A1:F1 is numbered 1,2,3,etc
to avoid Column()

Man, Brian, that's a good trick! I've always used COLUMN().

Thanks buddy!

Todd,

I'd like to take credit for this, but I got this from Aladin.....

#### Greg Truby

##### MrExcel MVP
Bartoni,

=VLOOKUP(\$A3,Data,COLUMN()-0,0)

something like that. You can use the Column() function to make this "draggable" horizontally. Brian's solution has the advantage that if you later come back in and insert a column in the area with the Vlookup formulae (not the data area), your third argument doesn't get mucked up, whereas if you use Column(), you'd have to change what you're subtracting to compensate for the added column.

Note: I changed the cell locking on the first argument, both Brian and fairwinds did \$A\$3, you probably want to just lock the column - \$A3 so you can drag down too.

HTH

Replies
3
Views
217
Replies
0
Views
148
Replies
5
Views
262
Replies
7
Views
150
Replies
3
Views
82

1,187,175
Messages
5,962,044
Members
438,578
Latest member
MrJimC

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back