Help

Find the closest value and lookup different field

Topic Labels: Formulas
1840 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Ajay_Dhesikan
4 - Data Explorer
4 - Data Explorer

I have a table like this:
| level | exp |
|1|0|
|2|500|
|3|1,500|
|4|3,750|
|5|7,875|
|6|14,175|
|7|22,680|
|8|32,886|
|9|44,396|
|10|57,715|
|11|72,144|
|12|90,180|
|13|112,725|

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):
=IF(ISNUMBER(E2),INDEX(SORT(A:A,B:B,0),MATCH(E2,SORT(B:B,B:B,0),-1)),)
or
=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
Robert_Roberts
4 - Data Explorer
4 - Data Explorer

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

Eliyahu_Sandroy
4 - Data Explorer
4 - Data Explorer

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.