+ Reply to Thread
Results 1 to 4 of 4

VLOOKUP formula not working

  1. #1
    Registered User
    Join Date
    06-12-2013
    Location
    Fairbury NE
    MS-Off Ver
    Excel 2007
    Posts
    70

    VLOOKUP formula not working

    My formula is: =VLOOKUP($A2,Sheet1!A:D,4,0) and the message returned in the cell is #N/A (Value not available error).

    I've used this formula in other worksheets and it has worked fine. The lookup field is a number that can also have a - or letter assigned to it (i.e. 1010-B). I tried changing the numbers to text to see if that would help, but it did not.

    I am not sure how to solve and am not sure what I am missing. Any guidance is appreciated.

  2. #2
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,933

    Re: VLOOKUP formula not working

    Sometimes a space at the end is the problem.
    For better help we need the excelfile.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,391

    Re: VLOOKUP formula not working

    Hi and welcome to the forum

    That error message normally means it cannot find what you want it to look for. If you know that the value you are looking for is really there, do a =exact(cell1,cell2) to see if they really are the same.

    Check for leading and trailing spaces as well

    When all else fails, upload a sample workbook
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: VLOOKUP formula not working

    =exact(cell1,cell2) is not a good indicator as it's case sensitive
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    a simple =a1=b1 is a better test
    also exact tests text strings so a real number 100 compared with a text number 100 comes out as true ! that surprised me but it does but if you use
    =text number=real number you get false
    Last edited by martindwilson; 08-08-2013 at 05:31 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Vlookup formula not working
    By DKAbi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2012, 10:01 AM
  2. Excel 2007 : Vlookup formula not working
    By jana1120 in forum Excel General
    Replies: 2
    Last Post: 05-16-2012, 08:19 AM
  3. Vlookup Formula not working...
    By Swambo in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-15-2011, 05:31 PM
  4. Vlookup formula not working
    By rbwen in forum Excel General
    Replies: 7
    Last Post: 12-08-2011, 08:12 PM
  5. Why is my Vlookup formula not working?
    By Kleinstein in forum Excel General
    Replies: 2
    Last Post: 07-08-2010, 07:38 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1