Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Simple VLookup Problem (I hope!)

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Surrey, United Kingdom
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I have a simple array of data consisting of several columns. Each column holds data for a specific date.

    On a seperate worksheet I wish to display this data for each date and I am using lookup functions to get it. I am using the following formula:

    VLOOKUP($A1,'Sheet2'!$A1:$AE273,3,0)

    All I want to do is drag this formula across my cells so that the column reference number (3 in the example above) will increase by 1 for each column. No matter what I try the column ref will always remain fixed.

    Can anyone help? It is driving me insane!

    Many thanks,

    Nibbles


  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-16 02:53, Nibbles wrote:
    Hi,

    I have a simple array of data consisting of several columns. Each column holds data for a specific date.

    On a seperate worksheet I wish to display this data for each date and I am using lookup functions to get it. I am using the following formula:

    VLOOKUP($A1,'Sheet2'!$A1:$AE273,3,0)

    All I want to do is drag this formula across my cells so that the column reference number (3 in the example above) will increase by 1 for each column. No matter what I try the column ref will always remain fixed.

    Can anyone help? It is driving me insane!

    Many thanks,

    Nibbles
    Which cell did you put this formula?

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Surrey, United Kingdom
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aladdin,

    That formula was just an example to illustrate the principal that I need. Say I have a column of data from A2:A273 on Sheet 1, and i wanted to use this to lookup data from A2:AE273 on sheet 2. I would want to enter the following formula in B2:

    Vlookup($A2,'Sheet2'!$A2:$AE273,2,0)

    I would then want to drag this formula across the range B2:AE273 on sheet 1. However when I do this the column ref will remain fixed at 2, whereas I want it to be 3 for column C, 4 for column D etc.

    Hope this explains my problem better,

    Nibbles

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-16 03:02, Nibbles wrote:
    Hi Aladdin,

    That formula was just an example to illustrate the principal that I need. Say I have a column of data from A2:A273 on Sheet 1, and i wanted to use this to lookup data from A2:AE273 on sheet 2. I would want to enter the following formula in B2:

    Vlookup($A2,'Sheet2'!$A2:$AE273,2,0)

    I would then want to drag this formula across the range B2:AE273 on sheet 1. However when I do this the column ref will remain fixed at 2, whereas I want it to be 3 for column C, 4 for column D etc.

    Hope this explains my problem better,

    Nibbles
    In B2 enter and copy across:

    VLOOKUP($A2,'Sheet2'!$A2:$AE273,COLUMN(),0)


  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Surrey, United Kingdom
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's fantastic Aladdin, thankyou very much, I knew it would be annoyingly simple!

    All the best,

    Nibbles

  6. #6
    New Member
    Join Date
    Apr 2002
    Location
    Centerville, Indiana
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Aladin. I was stumped also and you solved the problem.

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
  •