Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Find the closest value and lookup different field

Topic Labels: Formulas
670 3
Showing results for 
Search instead for 
Did you mean: 

I have a table like this:
| level | exp |

I want a formula that outputs the right level when an exp number that is within a range is given. For example:
With an input of 700, the formula should display 2 because the closest available exp value (rounded down) to 700 is 500 and the corresponding level is 2.
With an input of 10,000, the formula should display 5 because the closest available exp value (rounded down) is 7,875 which corresponds to level 5.
With an input of 33,886, it should output 8.

The solution in sheets would be something like (with E2 being the input):
=QUERY(B:A,"select A where B >= "&E2&" order by B limit 1",0)

How is it possible to do this in Airtable? Thanks!

3 Replies 3

Did you ever find anything out about how to do this? Needing something similar myself…

Did you find any solution for this?

Welcome to the Airtable community!

The formula field for this situation would be extremely complex and would be very difficult to scale up.

A better approach would be to use a script (either from a button or an automation). Note that scripts did not exist back when this question was first proposed.