NUMBERS: Lookup hourly rental rate based on date of transaction

Question

I have a resource that has an hourly rental rate. That hourly rate has changed (increased) over time. To capture that rate history, I have created a table with the date of the rate change and the new rate that looks like this:

Date of rate change | Rate

2015-01-01 | $60

2017-01-01 | $65

2017-07-13 | $68

I am trying to create a formula that will look up the hourly rate based on the date of the transaction/rental and then determine what the rental rate would have been on that date. I figure there must be some way to do this with a vlookup but from the documentation, it looks like that function requires an exact match and I want to match based on the input date being after the date in the lookup table not an exact date.

Any ideas?

Answer 1

Ok, so now had chance to test, use this formula :

=IF(C2<=A4,B4,IF(C2<=A3,B3,IF(C2<=A2,B2)))

The date tested is in cell C2 and the three dates are in A2, A3 and A4, while the three rates are in B2, B3 and B4.

The logic has been reversed ie test for the oldest date first.

You could also use names for the 3 rates, but it will work the same.