Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLOOKUP Error (2003)

    I am using VLOOKUP and ran into an oddity I cannot figure out. Following is the formula I am using:
    =IF($C16>0,VLOOKUP($C16,'Weekly Data Dump'!$B$2:$C$1000,2,FALSE),)

    C16 contains "985" and successfully finds "985" on the "Weekly Data Dump" worksheet and gives me the appropriate info.

    Problem is with row 17. Same basic formula:
    =IF($C17>0,VLOOKUP($C17,'Weekly Data Dump'!$B$2:$C$1000,2,FALSE),)

    C17 contains "1015", and I get #NA. Matter of fact, I get #NA for any number over 1000.

    Can anyone tell me why this is happening?

    Thank you!
    Mary

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VLOOKUP Error (2003)

    We'd need to see (a copy of) the workbook.

  3. #3
    Lounger
    Join Date
    May 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP Error (2003)

    Thanks for the quick reply, Hans.

    Attached is a stripped down version of the file.

    I greatly appreciate any insight you may have into the problem.

    Mary

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VLOOKUP Error (2003)

    Your lookup formula only looks down as far as row 393 so it doesn't look at any numbers over 1000.
    Change it to: <code>=IF($A3>0,VLOOKUP($A3,'Weekly Data Dump'!$A$2:$B$498,2,FALSE),)</code>
    or set up a dynamic range that will expand as you add data.
    Regards,
    Rory
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Lounger
    Join Date
    May 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP Error (2003)

    Great. Thank you.

    You mentioned setting up a dynamic range. This sounds like what I need for a long-term solution as my "data dump" sheet will grow weekly.

    What would I need to change in my formula to make it dynamic?

    Thank you,
    Mary

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VLOOKUP Error (2003)

    See the attached (I had a feeling you might ask! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> ) Select Insert->Name->Define from the menu to view the name definition.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VLOOKUP Error (2003)

    Select Insert | Name | Define...
    Enter Data in the 'Names in workbook' box.
    Click in the 'Refers to' box and enter the following formula:
    <code>
    =OFFSET('Weekly Data Dump'!$A$2,0,0,COUNTA('Weekly Data Dump'!$A:$A)-1,2)
    </code>
    Click Add, then close the dialog.
    Now change the formula in cell B3 on the Assessments sheet to
    <code>
    =IF($A3>0,VLOOKUP($A3,Data,2,FALSE),)
    </code>
    and fill down.

  8. #8
    Lounger
    Join Date
    May 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP Error (2003)

    Thank you, thank you, thank you!!!

  9. #9
    Lounger
    Join Date
    May 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP Error (2003)

    Thank you for your help!

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VLOOKUP Error (2003)

    Glad to help. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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