Lookups-changing the column ref when dragging formula

Helen · Feb 20th, 2002, 12:14 PM

When I do a lookup, and I drag the lookup down/across, is there any way I can make the column/row number change as I drag?

e.g. =VLOOKUP(A1;$E$1:$F$6;2;FALSE)

when I then drag the formula down the
2 becomes a 3 then a 4 etc.

Thanks

Juan Pablo González · Feb 20th, 2002, 12:32 PM

Change it to:

=VLOOKUP(A1;$E$1:$F$6;ROW(A2);FALSE)

And drag down.

Helen · Feb 21st, 2002, 11:16 AM

Thanks for the help, call me stupid but I don't understand it !!! I have tried seting the scenario up, but it doesn't seen to work?

Feb 21st, 2002, 11:20 AM

is it the semicolons messing it up rather than commas ?

vlookup(a1,range,3,false)

you have vlookup(a1;range;3 etc)

hth
Chris D

Helen · Feb 22nd, 2002, 07:30 AM

No, my computer is set to use semi-colons because of a piece of software I have.

I just didn't understand the formula!!!

Feb 22nd, 2002, 09:05 AM

oh !

well =row(a7) is asking excel to return the value of the row number of cell A7.... which is 7

so row(aa239) will equal 239

As you drag down A2 becomes A3 then A4 then A5 etc etc

so, row(a2)=2 and row(a3)=3 and row(a4)=4 etc etc

which is putting what you wanted into each succesive line of formula without having to type it in each time : you do it once with row(a2) and it takes care of the logic of incremental relativity

Yet another good one from our MVP

Helen · Feb 22nd, 2002, 09:14 AM

I understand now - takes a while sometimes. thanks very much for your help.

Cool smiley too !!!

Feb 20th, 2002, 12:32 PM	#2
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	Change it to: =VLOOKUP(A1;$E$1:$F$6;ROW(A2);FALSE) And drag down. __________________ Regards, Juan Pablo González http://www.juanpg.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 20th, 2002, 12:14 PM	#1
Helen Board Regular Join Date: Feb 2002 Posts: 103	When I do a lookup, and I drag the lookup down/across, is there any way I can make the column/row number change as I drag? e.g. =VLOOKUP(A1;$E$1:$F$6;2;FALSE) when I then drag the formula down the 2 becomes a 3 then a 4 etc. Thanks

Feb 21st, 2002, 11:16 AM	#3
Helen Board Regular Join Date: Feb 2002 Posts: 103	Thanks for the help, call me stupid but I don't understand it !!! I have tried seting the scenario up, but it doesn't seen to work?

Feb 21st, 2002, 11:20 AM	#4
Guest Posts: n/a	is it the semicolons messing it up rather than commas ? vlookup(a1,range,3,false) you have vlookup(a1;range;3 etc) hth Chris D

Feb 22nd, 2002, 07:30 AM	#5
Helen Board Regular Join Date: Feb 2002 Posts: 103	No, my computer is set to use semi-colons because of a piece of software I have. I just didn't understand the formula!!!

Feb 22nd, 2002, 09:05 AM	#6
Guest Posts: n/a	oh ! well =row(a7) is asking excel to return the value of the row number of cell A7.... which is 7 so row(aa239) will equal 239 As you drag down A2 becomes A3 then A4 then A5 etc etc so, row(a2)=2 and row(a3)=3 and row(a4)=4 etc etc which is putting what you wanted into each succesive line of formula without having to type it in each time : you do it once with row(a2) and it takes care of the logic of incremental relativity Yet another good one from our MVP

Feb 22nd, 2002, 09:14 AM	#7
Helen Board Regular Join Date: Feb 2002 Posts: 103	I understand now - takes a while sometimes. thanks very much for your help. Cool smiley too !!!