+ Reply to Thread
Results 1 to 14 of 14

VLOOKUP not working properly

  1. #1
    Registered User
    Join Date
    05-31-2017
    Location
    Brunswick, ME
    MS-Off Ver
    2010 (work) and 2016 (home)
    Posts
    68

    VLOOKUP not working properly

    NOTE: This thread is a tangential continuation from this one.

    I do not know why my VLOOKUP column is only working for 4 cells. I've converted everything to numbers and checked for extra spacing, and I don't know what else to do.

    For example, there is no difference between A21 and B31 (except for cell reference)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    And yet, A21 yields a "N/A" error, and A31 gives the correct value.

    Please see attached sheet.
    Attached Files Attached Files

  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 not working properly

    This must be related to the Floating Point Precision issue.

    Try
    =VLOOKUP(ROUND(B21,0),'Rand #'!$A$2:$B$61,2,FALSE)

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,574

    Re: VLOOKUP not working properly

    I'm confused, in A21 you are looking up the value in B21 which is 10 and in A31 you are looking up the value in B31 which is 16.

    Anyway, there is a rounding problem, you'll see it if you increase the no of decimals in column B, but you could try using ROUND to deal with that.

    =ROUND(SUMPRODUCT((C2 >=Row)/COUNTIF(Row,Row)),0)
    If posting code please use code tags, see here.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: VLOOKUP not working properly

    b21 in sheet locater new does not excist in column A of sheet rand #.

    That is why you get the result N/A (not available).

    b31 in sheet locater new excist in column A of sheet rand #. (and that is why it returns the value 30, the value from column B (the 2 in the VLookup formula).
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,370

    Re: VLOOKUP not working properly

    Most of the numbers don't exist in the Lookup Table.
    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


  6. #6
    Registered User
    Join Date
    09-26-2011
    Location
    Somewhere, Someplace
    MS-Off Ver
    Excel 2016, 64-Bit
    Posts
    2

    Re: VLOOKUP not working properly

    Hi,

    You're getting #N/A values because what your looking up from column A (Locator_new sheet) doesn't exist in the lookup table (Rand # sheet) and you've asked for an exact match by specifying the FALSE parameter. If you use the TRUE parameter VLOOKUP will find the next closest value that is less than the value in column B (Locator_new sheet) in the lookup table (Rand # sheet) and return the appropriate value. So for example, if you us the TRUE parameter in cell A30 (Locator_new sheet) VLOOKUP will return 32 because the 15 doesn't exist in column A of the lookup table. It finds 10 as the next closest number that is less than 15 and returns the corresponding 32 from column B.

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

    Re: VLOOKUP not working properly

    @Norie and oeldere

    Are you guys looking at a different attachment than I am?
    I don't see any decimal value in column B on locator_new sheet. Column B is all whole numbers with 16 decimals showing.

    The value of B21 in Locator_new sheet is 10
    On the Rand Sheet, A2 is 10
    So the match is certainly there.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,574

    Re: VLOOKUP not working properly

    Jonmo1

    I don't know how many decimals I went out to but I started seeing values like this,

    70.000000000000100000000000

    which is in row 137 and this

    312.999999999999000000000000

    in row 667.

    Just checked and it was at about 13 decimal places I started seeing these, and similar values.

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

    Re: VLOOKUP not working properly

    ok, now I see them further down (i never scrolled down past the rows related to the question, 21 and 31)
    In row 21 and 31, there are no decimals, they are whole numbers.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,974

    Re: VLOOKUP not working properly

    Just spotted Norie's numbers!
    Last edited by JohnTopley; 05-31-2017 at 03:35 PM.

  11. #11
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: VLOOKUP not working properly

    look at attachment
    if you see #N/A = value from B2 doesn't exist in defined range
    Attached Files Attached Files
    Last edited by sandy666; 05-31-2017 at 03:59 PM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  12. #12
    Registered User
    Join Date
    05-31-2017
    Location
    Brunswick, ME
    MS-Off Ver
    2010 (work) and 2016 (home)
    Posts
    68

    Re: VLOOKUP not working properly

    Quote Originally Posted by Jonmo1 View Post
    This must be related to the Floating Point Precision issue.

    Try
    =VLOOKUP(ROUND(B21,0),'Rand #'!$A$2:$B$61,2,FALSE)
    Jonmo1,

    Thanks! That's what I needed!

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

    Re: VLOOKUP not working properly

    You're welcome.

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: VLOOKUP not working properly

    Harrumph. PCs are thick. they can only (sometimes) count in binary. The Venus Fly Trap can count to 2....

    Anyhow (I can't explain it...) but rounding works.
    Glenn



+ 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. VLOOKUP Not Working Properly
    By Cremorneguy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2014, 03:09 PM
  2. Vlookup Not Working Properly
    By trotm in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-30-2014, 08:29 AM
  3. Vlookup not working properly
    By JESSIER4025 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-25-2014, 04:16 PM
  4. VLOOKUP not properly working
    By amphinomos in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-01-2013, 05:35 AM
  5. VLOOKUP() not working properly
    By ckgeary in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-28-2013, 01:32 PM
  6. [SOLVED] vlookup not working properly
    By djmatok in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-25-2013, 12:19 PM
  7. Vlookup not working properly
    By b_motl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-15-2008, 03:12 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