@ > 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)