Lookup a number in a sliding scale table

[HSY-R]

Hi,

Link

This workbook has a sheet called ‘Test’ which has fields that include stops & mileage.

These are the x & y axis fields of the associated table ‘Sliding scale’

Y = mileage
x - stops

I am struggling to write a formula that can find the associated rate based on the number of stops & mileage.

Note, that mileage could be 112 and stops could be 73 so the formula would need to look in between nearest numbers to associate the correct row/column combo.

Answer to above should be 2.07

Can anyone help?

Please

[kuovonne]

Welcome to the Airtable community!

Your link is not working for me.

If you are trying to link to a specific record in a different table based on a combination of miles and stops, you might need to use a script instead of a formula. It is difficult to tell without more information.

[HSY-R]

Sorry, link updated. I have added some some rounded values so that there is an exact match to look for instead of looking for the closest match

[kuovonne]

You updated link gives editor permissions to whoever clicks the link.
I recommend that you change it to a read-only link so that you will not be billed for random people who click your link.

Or you could post screen captures instead. That would provide the most value to anyone who happens to read this post in the future.

Looking at the data, you cannot do this with a formula field (even with the rounded values). At least, not unless you can mathematically calculate the rate without having to use the table.

I recommend using a custom script to create the link for you.

[HSY-R]

Thanks,

Really appreciated.

Can anyone advise if they are able to help with a script?

[kuovonne]

If you are interested in hiring me to write a script, you can book a meeting with me.