Get value of the nearest number from a table

[Evonne_Matt]

Hi,

I would like to get the nearest value from a look up table. This is my excel formula but I couldn’t work out how to convert it to Coda.

=ROUND((TRUNC((A5),0)+0.99)*(VLOOKUP((TRUNC((A5),0)),!LU_Scale2,2))-VLOOKUP((TRUNC((A5),0)),!LU_Scale2,3),0)

This formula is in B5 and the look up table under it in this image below:

Any help would be greatly appreciated!

[Pablo_DV]

This brought back memories! Really happy to have left all that excel madness behind.

The vlookup is not taking the nearest, but the one that matches the closest without surpassing it. So In your example, the values from the row $=1,282 are taken even if 2,596 would be nearer.

So with this in mind, this is the formula I came up with

[Scale 2].Filter(
  [$]<=WeeklyEarnings.Round(0) #Get all the items in Scale 2 <= to your weekly earnings
).Sort(
  false,[Scale 2].[$] #Sort according to `$` from bigger to smaller
).First().Let(row, #store the row where `$` is biggest in the variable `row`
  round((WeeklyEarnings.Round(0)+0.99)*row.a-row.b,0) #the actual formula from your excel
)

Here you have the actual doc in action, it should behave exactly like your excel.

[Evonne_Matt]

Omg wow. You are a Coda God! Such an elegant way of feeding data that I would never even thought be possible haha! Coda newbie here, obviously.

Thank you SO MUCH!!

[Pablo_DV]

Thanks for the kind words! It’s not that advanced, but you need to assimilate a few concepts first - keep exploring and you’ll get there in no time.