Hi, “The Authorities” tell me the sales tax rate starting on 2016-07-01 is 8.25% , starting on 2018-07-01 it is 8.5%, and starting on 2020-07-01 it is 8.75%. I want to use the sales tax that applied on a date, e.g. 2018-07-01, in a formula.

So this is “lookup closest value less than or equal to date (2018-07-01) then return the value in the column next to it (8.5%).” I just read about VLOOKUP and it’s really close, hooray! :

if the exact SearchCriterion is not found, the last value that is

than the criterion will be returned.smaller

That means that if the sale date is the start date of a new tax rate, VLOOKUP will return the sales tax for the *previous* period. So I think all I have to do is have a column “*After* this day the sales tax changed” containing 2016-06-30, 2018-06-30, etc. and VLOOKUP() on that. Or is there a cleaner way?

It seems another way to do this would be to create a user-defined function `SalesTaxOnDate(date)`

with a series of `IF`

statements in it.