Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: VLOOKUP Returns #NA when data is present
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Mar 2015
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP Returns #NA when data is present

    Quote Originally Posted by Fluff View Post
    Does the ISNUMBER return true for both?
    ISNUMBER returns true on the old price sheet, and false on the new price sheet.

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VLOOKUP Returns #NA when data is present

    In that case the one on the new sheet is text not a number
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    Board Regular
    Join Date
    Mar 2015
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP Returns #NA when data is present

    Quote Originally Posted by Fluff View Post
    In that case the one on the new sheet is text not a number
    I changed it to numbers, that didnt seem to work, I did copy/pate into a notepad and pasted them back into that column and that seemed to change them to numbers, but i am still seeing NA on all the values.
    Last edited by i8ur4re; Sep 20th, 2019 at 04:35 PM.

  4. #14
    Board Regular
    Join Date
    Mar 2015
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP Returns #NA when data is present

    Quote Originally Posted by i8ur4re View Post
    I changed it to numbers, that didnt seem to work, I did copy/pate into a notepad and pasted them back into that column and that seemed to change them to numbers, but i am still seeing NA on all the values.
    Here is the link to the document if you dont mind taking a look at this, I have no words for how fraustrating this is.

    https://drive.google.com/file/d/1J3W...ew?usp=sharing

  5. #15
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,431
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VLOOKUP Returns #NA when data is present

    I do not see 220175 or any of the 3xxxxx product numbers (D2:D15) in A2:A139 of the "New Prices" worksheet.

    I suspect that is true in many/most/all cases.

    I do not see 714116 in both worksheets. I see 714116 in "Old Prices", but 714166 in "New Prices".

    Use Data > Filter to spot-check product numbers.

    -----

    Change the range in the VLOOKUP to use absolute cell references, to wit:

    =VLOOKUP(D2,'New prices'!$A$2:$I$139,9,FALSE)

    Otherwise, your range is changing to A3:A140, A4:A141, etc as you copy the formula down the column.

    That might explain why some product numbers cannot be found in both worksheets.

    -----

    FYI, you can use $I$139 for both VLOOKUPs, instead of $E$139, even though you return the value from relative column 5. IMHO, it just makes things easier.

    To that end, you can also change D2 to $D2. That allows you to copy the formula from column K to column L. It is less error-prone.

Some videos you may like

User Tag List

Tags for this Thread

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
  •