+ Reply to Thread
Results 1 to 6 of 6

VLOOKUP Returns different results, depending on table length

  1. #1
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    219

    VLOOKUP Returns different results, depending on table length

    I have what I thought was a simple, straightforward application of VLOOKUP; however, when I programmed it, it didn't work. In an attempt to diagnose the problem, I shortened the table by just deleting one of the number '8's from the table length (so, the end of the table is on row 8, instead of row 88) - and then VLOOKUP worked properly for my shortened length.

    Would someone please tell me what I'm missing and how to fix this VLOOKUP to function properly for the entire column?
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: VLOOKUP Returns different results, depending on table length

    Try this:

    =VLOOKUP(C5,G$5:H$88,2,0)

    The 0 at the end ensures an exact match.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: VLOOKUP Returns different results, depending on table length

    You're missing an argument from the formula. Add the red part below.
    =VLOOKUP(C15,G$5:H$88,2,0)

    BSB

  4. #4
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    219

    Re: VLOOKUP Returns different results, depending on table length

    Thank you to both BadlySpelledBouy and AliGW! You've given me the same suggestion and it works. Great!

    In addition to getting a solution that works, I would like to advance my understanding:
    Can either of you (or anyone else) help me understand WHY that change worked? When I look-up the syntax of VLOOKUP, I see that the final argument is optional; e.g., per the following post
    https://www.ablebits.com/office-addi...rial-beginner/

    Why does adding an optional argument cause the formula to work (especially when setting the optional argument to '0' causes VLOOKUP to require an exact match, and omitting the argument allows looser matches)?

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: VLOOKUP Returns different results, depending on table length

    It's not so much that omitted (or set to 1 or TRUE) allows for "looser" matches -- it changes the search algorithm.

    When the fourth argument is 0/FALSE, VLOOKUP() performs an exact match "linear" search. It starts at the top of the list, looks at each value in the left column, and stops when it finds an exact match. This kind of search algorithm does not have any special requirements, only returns an exact match (though you can look at the wildcard examples), and is very slow.

    When the fourth argument is TRUE/1/omitted, Excel will use something very similar to a binary search. It looks at the first, last, and middle entries, decides which "half" of the list the lookup value belongs in, then cuts that section in half and so on until it has narrowed the search down to two consecutive entries. It then returns the first of the two consecutive entries. This search algorithm is much faster than the linear search, but it does require that the lookup column be sorted in ascending order (your lookup values are not sorted). When the values are properly sorted, then the result will be the closest value to lookup value that is just less than/before lookup value. Your ablebits link includes an example of this kind of search algorithm (the animal max speed example about half way down). https://www.ablebits.com/office-addi...ximate-vlookup

    When you try to perform a binary search on an unsorted list, you get erratic results because the search algorithm gets lost in different, incorrect parts of the list.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    219

    Re: VLOOKUP Returns different results, depending on table length

    Thank you, MRShorty! That is exactly the sort of explanation I was looking for - and, with this context, it is an explanation that I'll always remember. Your explanation clarified perfectly why I was getting different results when I changed the length of the table.

    It also turns out that I can sort the table, so I have done so and am now completing this portion of my program much more rapidly. Thank you, again.

+ 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 results from Datedif (employee length of svcs related)
    By Froogle in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-27-2018, 10:31 AM
  2. [SOLVED] Vlookup returns results in some cells others shows N/A
    By martin.paliarik in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-19-2017, 03:16 AM
  3. [SOLVED] VLOOKUP returns results to left of primary key
    By bryanhope in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-09-2017, 05:50 PM
  4. [SOLVED] Vlookup returns error even when Lookup Value and Value on the table is the same
    By jcanlas in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-28-2015, 04:40 AM
  5. [SOLVED] function that returns interest with different values depending previous cell's results
    By managingcrap in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-09-2013, 03:15 AM
  6. Possible to link Table length to Pivot table length?
    By saber0091 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-04-2013, 05:36 PM
  7. [SOLVED] vlookup fails on data from a pivot table-function returns N/A
    By Pat in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-17-2005, 05:05 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