v lookup formula autofil

bartoni

Active Member
Joined
Jun 10, 2003
Messages
296
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
Joined
Jun 10, 2003
Messages
296
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
Joined
Feb 16, 2002
Messages
8,459
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
Joined
Aug 29, 2002
Messages
3,042

ADVERTISEMENT

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
Joined
Feb 16, 2002
Messages
8,459
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..... :LOL:
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
Bartoni,

To answer your question regarding Fairwind's recommendation:

=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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,095
Messages
5,768,053
Members
425,451
Latest member
JohnBrooksBiddle

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top