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 smaller than the criterion will be returned.
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.