+ Reply to Thread
Results 1 to 20 of 20

VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

  1. #1
    Registered User
    Join Date
    01-11-2016
    Location
    Copenhagen
    MS-Off Ver
    2010
    Posts
    8

    VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    I'm using VLOOKUP function to match tasks (one worksheet) to assets (another worksheet). The key number is "ID" column, which is on both sheets.

    I get #N/A error if I use 'Range lookup' =0 or False.
    I get wrong data, if the 'Range lookup' is empty.

    I have checked many possible error reasons: sorting, length of Lookup value

    I've inserted formula in Assets (worksheet) on P2.
    I would like top match the whole Tasks worksheet and paste it to Assets.

    Anyone can help me with that?

    Thanks
    Cheers.
    Ela
    Attached Files Attached Files
    Last edited by Ebalinska; 01-14-2016 at 04:28 AM.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    It seems you want it to find a mach in column C, and return a value from column A
    Vlookup doesn't work that way.
    The way you have it, it would be searching column A on the Tasks sheet, for a match to the lookup value N2.
    But it doesn't exist, because those values appear to be in column C.

    You probably need Index and Match.
    Try this in P2 on the Assets sheet.

    =INDEX(TASKS!$A$1:$A$145,MATCH(N2,TASKS!$C$1:$C$145,0))

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,150

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    The Named Range, opgaveliste, should be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    01-11-2016
    Location
    Copenhagen
    MS-Off Ver
    2010
    Posts
    8

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    Thank you, but...

    When I paste formula

    =INDEX(TASKS!$A$1:$A$145,MATCH(N2,TASKS!$C$2:$C$145,0))

    I get another error:
    that formula contains en error.

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,806

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    Perhaps replace commas in the formula with semi-colons ( ; ) ? ( European regional settings) ?

    EDIT both ranges should be the same length I think C1:C145 in the formula or A2:A145
    Last edited by Pepe Le Mokko; 01-12-2016 at 04:19 AM.

  6. #6
    Registered User
    Join Date
    01-11-2016
    Location
    Copenhagen
    MS-Off Ver
    2010
    Posts
    8

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    I corrected it, but now I'm getting another error: #Name (#navn- in Danish)

    Attached Images Attached Images

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,309

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    Jonmo1's formula in your posted book works O.K. for me.

  8. #8
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,882

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    Try,

    P2=IFERROR(INDEX(TASKS!$A$1:$K$145,MATCH($N2,TASKS!$C$2:$C$145,0)+1,MATCH(P$1,TASKS!$A$1:$K$1,0)),"") and drag down.

    Check the attached file.
    Attached Files Attached Files
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,150

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    Why not just adjust the Named Range as I suggested in pot #3? Or, if the Named Range is used elsewhere, create another one.

    Regards, TMS

  10. #10
    Registered User
    Join Date
    01-11-2016
    Location
    Copenhagen
    MS-Off Ver
    2010
    Posts
    8

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    Thanks.

    But 'ID' is not a unique number/value on one worksheet (Tasks). Because there are more tasks on one asset.Se for example line 37,38 on TASKS worksheet. The 'ID' nr is the same 2827.
    I can't get both values
    Last edited by Ebalinska; 01-12-2016 at 08:49 AM.

  11. #11
    Registered User
    Join Date
    01-11-2016
    Location
    Copenhagen
    MS-Off Ver
    2010
    Posts
    8

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    Quote Originally Posted by TMS View Post
    Why not just adjust the Named Range as I suggested in pot #3? Or, if the Named Range is used elsewhere, create another one.

    Regards, TMS
    I tried to change the range as you suggested, but I couldn't make it work.

  12. #12
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,806

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    Quote Originally Posted by Ebalinska View Post
    I corrected it, but now I'm getting another error: #Name (#navn- in Danish)


    index is INDEKS in Danish
    match is SAMMENLIGN

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,150

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    See the updated sample workbook attached.
    Attached Files Attached Files

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,150

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    Note: a basic VLOOKUP or INDEX/MATCH will only return the FIRST matching record.

  15. #15
    Registered User
    Join Date
    01-11-2016
    Location
    Copenhagen
    MS-Off Ver
    2010
    Posts
    8

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    ok.
    So is there any command, which can solve that (I mean, if there are more than one matching cell)?
    Thank you all for help.

    Btw. It is a great forum (it is my first post). I didn't expect so quick response and so kind people :-)
    Last edited by Ebalinska; 01-12-2016 at 03:37 PM.

  16. #16
    Registered User
    Join Date
    01-11-2016
    Location
    Copenhagen
    MS-Off Ver
    2010
    Posts
    8

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    Quote Originally Posted by TMS View Post
    Note: a basic VLOOKUP or INDEX/MATCH will only return the FIRST matching record.
    Now I'm trying the other way around. Match assets to tasks. I use the same formula, as you used, but it doesn't work (TASKS worksheet,formulas are in cells: M2, N2).
    I've sorted values(lookup values) in both worksheets.
    Should I format cells before writting the formula? Formula in my opinion is fine.
    Please see attached file.

    Thanks
    Attached Files Attached Files

  17. #17
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,150

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    Again, it is because you are trying to use a Named Range that is inappropriate. Your original formula is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    But BYGDEL refers to
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    However, the ID is in column N. And the data to be returned, I think, is in column P. So I have created a new Named Range, BYGDEL2 which refers to
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    And I have changed the formula to
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The value being searched for must be found in the first column of the lookup table/range. In this case, it is column N.

    See the updated example.


    Regards, TMS
    Attached Files Attached Files

  18. #18
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,150

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  19. #19
    Registered User
    Join Date
    01-11-2016
    Location
    Copenhagen
    MS-Off Ver
    2010
    Posts
    8

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    Quote Originally Posted by TMS View Post
    Again, it is because you are trying to use a Named Range that is inappropriate. Your original formula is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    But BYGDEL refers to
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    However, the ID is in column N. And the data to be returned, I think, is in column P. So I have created a new Named Range, BYGDEL2 which refers to
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    And I have changed the formula to
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The value being searched for must be found in the first column of the lookup table/range. In this case, it is column N.

    See the updated example.


    Regards, TMS


    Thanks for detailed explanation.

  20. #20
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,150

    Re: VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)

    You're welcome. Thanks for the rep.

+ 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. How to use vlookup if value of lookup cell is same in lookup range
    By anujteetwal in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-09-2014, 05:15 AM
  2. [SOLVED] VLOOKUP gives wrong result with TRUE for range-lookup
    By drfarmkid in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-06-2013, 06:54 PM
  3. Help with Vlookup- lookup value range?
    By asburytl in forum Excel General
    Replies: 7
    Last Post: 08-04-2011, 02:24 PM
  4. [SOLVED] VLOOKUP range lookup
    By xtreme_fac in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-18-2005, 06:50 AM
  5. Replies: 2
    Last Post: 11-22-2005, 05:12 PM
  6. Range Lookup in VLookup ?'s
    By Bronco_Junkie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2005, 06:20 PM
  7. Range Lookup in VLookup ?'s
    By Bronco_Junkie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2005, 06:17 PM

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