Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: VLOOKUP problem #NA error

  1. #1
    Board Regular
    Join Date
    Feb 2007
    Location
    texas
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VLOOKUP problem #NA error

    This is a weird one I am trying to wrap my head around.

    I created a workbook with three sheets, I do a vlookup formula that looks like this:=VLOOKUP(D3,Sheet3!A:D,2,FALSE)

    so basically, find the value of D3 and look for the exact match in sheet 3 (column range a-d) then report back the value found in the second column.

    I get an #NA error with this. Funny thing is that if I go to sheet 3, find the correct value and "re-type" it in, it will now pull the information I want.

    I've tried some basic formatting changes that dont fix the issue and the only thing that seems to work is retyping the values into sheet 3.

    any ideas?

    I've got about 1500 rows I'd have to retype so the idea doesnt excite me.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,055
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: VLOOKUP problem #NA error

    Try to run the free ASAP Utilities on the table range...

  3. #3
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VLOOKUP problem #NA error

    The obvious thing to check is whether you have leading or trailing spaces in the values in Sheet3.

  4. #4
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,728
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VLOOKUP problem #NA error

    The #N/A is being returned because it is not finding an exact match, but when you retyped it does, this would tend to indicate that what you retyped in the cell is not exactly what was there before.

    Perhaps there are extraneous spaces or non-breaking spaces in the values in the lookup table
    Office 2010/365

  5. #5
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,535
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VLOOKUP problem #NA error

    If it's just a formatting issue, you can usually apply the appropriate format to that range and goto Data-->Text to Columns-->Finish to apply it.

    HTH,

    Smitty

  6. #6
    Board Regular dave3009's Avatar
    Join Date
    Jun 2006
    Location
    Inverclyde, Scotland
    Posts
    6,996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VLOOKUP problem #NA error

    You could try something like this instead

    =VLOOKUP(D3,TRIM(Sheet3!A:D),2,FALSE) confirmed with Ctrl+Shift+Enter

    Seemed to do the job for me, to my surprise.

    KR


    Dave
    Please state your version of Excel, I use Excel 2007 on Win 10.
    Back up all data and test VBA code on a COPY workbook, and please use [code][/code] tags.
    HTML Maker | HTML Maker - FAQ

  7. #7
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,632
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP problem #NA error

    folks, the question is, why does dave's method work and not the others? I am having similar issues and have tried each of the "normal" fixes with no luck. doing an array formula down the entire page is going to take sometime. strangely enough, dave's formula stops working on my sheet if you manually type in the lookup value.
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,055
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: VLOOKUP problem #NA error

    Quote Originally Posted by ajm View Post
    folks, the question is, why does dave's method work and not the others? I am having similar issues and have tried each of the "normal" fixes with no luck. doing an array formula down the entire page is going to take sometime. strangely enough, dave's formula stops working on my sheet if you manually type in the lookup value.
    I don't think you have tried ASAP Utilities to clean up stray spaces that apparently exists in your table area.

  9. #9
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,632
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP problem #NA error

    Aladin,

    I started over and used ASAP Utilities to cleanse both the lookup values and the table array of leading and trailing spaces. When that didn't work, I tried the "Leading, Trailing, and Excessive" spaces option. Nada.
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,055
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: VLOOKUP problem #NA error

    Quote Originally Posted by ajm View Post
    Aladin,

    I started over and used ASAP Utilities to cleanse both the lookup values and the table array of leading and trailing spaces. When that didn't work, I tried the "Leading, Trailing, and Excessive" spaces option. Nada.
    If TRIM works, the options you tried will work 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
  •