@ > Home > Contents > Upper limit VLOOKUP Excel4+, SpreadCE, Ooo, Lotus


=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 maximum),
=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)