Thanks:  0
Likes:  0

# Thread: Lookups-changing the column ref when dragging formula

1. 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

2. Change it to:

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

And drag down.

3. 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?

4. 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

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

I just didn't understand the formula!!!

6. 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

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

Cool smiley too !!!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•