v lookup formula autofil

[bartoni]

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

 

[fairwinds]

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]

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]

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]

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]

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]

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