Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Lookups-changing the column ref when dragging formula

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Change it to:

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

    And drag down.

    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Default

    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. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Default

    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. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Cool smiley too !!!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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