CODA´s equivalent for VLOOKUP´s nearest match

1

Hi! I wasn´t able to find a solution for this.
To explain it as simple as posible, I have 2 tables:

Table 1 - I have US dollar exchange rates for every business day
Column A - Date
Column B - USD exchange rate

Table 2 - I have transactions
Column C - Transaction date
Column D - USD exchange rate

So in column D I have a lookup, where I search for USD exchange rate in Table 1, for the date in Column C. That is easy to do.
= Table1.Filter(ColumnA=ColumnC).ColumnB.Sum()

The problem is when the transaction occurs on a non-business day, like weekend.

So in excel, I had a vlookup where the last parameter was TRUE, so when the formula didn´t find the exact date on Table 1, it will return the nearest match (less than the date it was searching).
So if the Transaction date is 6 July 2019 (saturday) for example, it will not find it on Table 1, so it will return the exchange rate of 5 July 2019.

Is something like this possible on Coda?
Thanks!!!

2

yes , you could write Table1.Filter(ColumnA <= Column D).ColumnB.Last basically pull out all records from Table1 with date equlas to OR earlier than date on transcations table and then pick the last entry (so you get latest rate)`

Not entirely sure why you would do sum - if there is a single exchange rate you are looking for. here’s an example

1 Like
3

Krunal, thanks so much for your time and response. It worked perfectly!

Yes, the sum() part is wrong, but it was the only way I knew to get the data I wanted. I still have a lot to learn about Coda.

Thanks!