3 Replies Latest reply: May 23, 2011 9:23 PM by 4thSpace
4thSpace Level 1 Level 1 (0 points)

I have the table below.  The formula in B3 is causes the VLOOKUPS in C3 to be off.  If I just use the value 0.20 in B3, I get "sixty" in C3.

vlookup.jpg

 

The B3 formula is:

 

=ABS(A2-A4)

 

and C3 is:

 

=VLOOKUP(B3,D1:E5, 2)

 

What needs to change for this to work correctly?

  • 1. Re: Why does this formula break VLOOKUP?
    Wayne Contello Level 6 Level 6 (13,620 points)

    You need to use an optional argument for VLOOKUP():

     

    VLOOKUP(search-for, columns-range, return-column, close-match)

     

    close-match: An optional value that determines whether an exact match is required.

     

    • close match (TRUE, 1, or omitted)If there’s no exact match, select the column with the largest top-row value that is less than the search value. Wildcards can’t be used in search-for.
    • exact match (FALSE or 0)If there’s no exact match, return an error. Wildcards can be used in search-for.

     

    If you update the formula in Cell C3:

    =VLOOKUP(B3,D1:E5, 2, 1)

     

    You will get the answer you expect.

     

    Regards,

    Wayne

  • 2. Re: Why does this formula break VLOOKUP?
    Barry Level 7 Level 7 (29,180 points)

    Functionally, there's no difference between the original VLOOKUP formula, where the 4th argument is omitted, and the one suggested by Wayne, where the 4th argument is given the value 1 (or TRUE).

     

    As described in the excerpt from the Formulas and Functions User Guide shown in Wayne's post:

    Wayne Contello wrote:

     

    • close match (TRUE, 1, or omitted): If there’s no exact match, select the column with the largest top-row value that is less than the search value.

    Reproducing the table shown on my own machine produces the value (fifty-five) with either version of the formula.

     

    I suspect that the result in C3, like any other spreadsheet cal\culation, is subject to a rounding error caused when the values in A2 and A4 are translated to binary for the calculation, then reinterpreted as a decimal value for the display. If C3 is set to show two decimal places, the actual value could be anything in the range 0.195 - 0.2099999999999. VLOOKUP uses the actual value, not the displayed value, so any actual value in the range 0.195-0.1999999999999999999 will display in C3 as 0.20, but will return the result for 0.10, 'the largest search-for value that is less than the search value.'

     

    Try revising your C3 formula to =ROUND(ABS(A2-A4),2)

     

    On my test table, this produced the expected result (sixty).

     

    Regards,

    Barry

  • 3. Re: Why does this formula break VLOOKUP?
    4thSpace Level 1 Level 1 (0 points)

    Thanks Barry.  As you noted, adding the optional value to VLOOKUP doesn't work.  But updating with ROUND did.

     

    Unfortunately, I accidentally selected my response as the correct answer.  I can't undo that or delete the message.  I did a helpful answer and like on Barry's response.