How to do a range lookup

Ask for Help
1

Trying to do the equivalent of a range lookup with VLOOKUP in Excel.

I have a list of boat lengths, and want to calculate fees based on what range they fall into.

Say I had the following cost structure in one of my tables:

| Boat Length | Fee  |
======================
| 0 - 10      | $10  |
| 10 - 20     | $25  | 
| 20 - 30     | $40  |
| 30 - 50     | $80  |
| > 50        | $100 |

If I had a boat length of 25 feet, I should be able to lookup in the cost table, and get $40

Thanks!

2

While it is possible as is, it would need string parsing with the way Boat Length is formatted.

I’d suggest you label your first column “Min Length”, and enter only the first number. Then, from another table, you might use the following formula:

prices = Prices.lookupRecords(sort_by="Min_Length")
for i in range(1, len(prices)):
  if prices.Min_Length[-i] <= $length:
    return prices.Fee[-i]

You have an example here: Tests - Grist.

3

Oh - Super easy if I just put on my python hat.

Thanks!