=VLOOKUP(A1,C1:D4,2) returns 2 for A1: 400 and the following range C1:D4. LOOKUP-Formulas return Offsets [from Value ... until less than higher Value].
0 1 400 2 800 3 3675 4
Now [from more than lower Value ... until Value] be required, returning the offset value of 1. This is practical in cases needing a maximum in a class, rather than a minimum (weights on ferries, luggage on airplanes and so on).
0,01 1 400,01 2 800,01 3 3675,01 4
The above change is one possibility (if 1 Cent more means exceeding the
=VLOOKUP(A1-0.01,C1:D4,2) another, with no change in data table needed.
But how does it work continuously in a mathematical sense?
Solution: (Bernd Plumhoff, see his VBA-Solution here)
1. Multiply a -1 containing cell with C1:C4 (Contents Insert Values Multiply) and sort C1:D4 upwards.
2. Copy C1:C3 (without D!) to C2 and replace C1 with -1E+9. If needed: Fit the lists highest lookup value or insert/append one. Our result:
-1E+9 4 -3675 3 -800 2 -400 1
3. Change A1 inside =VLOOKUP(-A1,C1:D4,2)