Why does this formula break VLOOKUP?

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

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