Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quirk with VLOOKUP (2003 SP2)

    My spreadsheet has a range of numbers illustrated by example in the attached file. The values in the range rise steadily to a maximum, then decline steadily after that. I want to find the row number where the value is some arbitrary amount less than the maximum value and have used VLOOKUP to accomplish the task. In the attached spreadsheet, enter a value of "100", and both instances of vlookup work properly. by returning "10"

    However when searching for "170", the second instance returns a value of "23", and when searching for "200", it returns a value of "30". I can understand that the VLOOKUP function would fail for values "on the downhill slope beyond the maximum", but it seems to fail even for values less than the maximum.

    Is this a bug in VLOOKUP, or is there another way to accomplish the task? In the real spreadsheet, the spreadsheet represents a cost versus age function. I want to find the range of ages where the cost is within a specified tolerance of the maximum value.
    Attached Files Attached Files
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quirk with VLOOKUP (2003 SP2)

    The reason that the lookup table must be sorted into assending order when using the "fuzzy" search is that the search algorithm uses a binary search. Therefore, it can get a hit anywhere in the table if the table is not sorted.
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quirk with VLOOKUP (2003 SP2)

    If you omit the 4th argument of VLOOKUP, the first column of the lookup array should be sorted in ascending order. If it isn't, the result is unpredictable. You could use

    =MATCH(A4,L9:L38,0)

    but this will fail if there is no exact match.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quirk with VLOOKUP (2003 SP2)

    Try these array formulas (confirm both with Ctrl+Shift+Enter):
    First index of number greater than or equal to that in A4:
    <code>
    =MIN(IF(A9:A38>=A4,ROW($1:$30)))
    </code>
    Last index of etc.:
    <code>
    =MAX(IF(A9:A38>=A4,ROW($1:$30)))
    </code>
    The highest number in the ROW function should equal the number of entries in your data column.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quirk with VLOOKUP (2003 SP2)

    Aha! Binary search. That makes sense why it was failing inpredictably. Thanks for the insight.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quirk with VLOOKUP (2003 SP2)

    Very Cool! I had used array formulae ONCE, many years ago, but never really understood them. Was baffled by your method, and doubly-baffled by the explanation in MS Help. Anyway, following your directions *literally* was all that it took to make it work. Thanks very much.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quirk with VLOOKUP (2003 SP2)

    A nice way to understand what is happening is as follows:
    - Select one of the cells with the array formulas.
    - Press F2 to edit the formula.
    - Select A9:A38>=A4 and press F9 to evaluate this expression,
    - You'll see an array of TRUE/FALSE values.
    - Select ROW($1:$30) and press F9 to evaluate it.
    - You'll see an array of the numbers 1 to 30.
    - Now select the entire IF(...) expression and press F9.
    - You'll see an array existing of FALSE for values that don't meet the condition, and the index number for values that do.
    - The MIN function only looks at numbers, so it ignores the FALSE values and returns the smallest index number, similar for MAX.
    - Press Esc to avoid saving the evaluated expressions.

Posting Permissions

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