Matching value accoring to a date from an asymetric date range

[GRA1990]

What I have is a table with tax rates.

TAXTABLE.jpg

Each date represents the day in which a new tax level has came into force . For example, on the 01/01/2020 a tax level of 36$ came into force and it replaced a tax level of 14 and it lasted till the 25/05/2020 when it change to a level of 25$ which is in force now.

There is no any consistent pattern in the gaps between the dates nor in the levels of the tax.

What I want to do is to create a range of dates with a step value of ONE DAY with a coresponding tax rate that was during this day. The first day sould be 01/01/2014 and the last is today (say 25/12/2020). Namely, some this like this (this is just a fragment, I need the whole table).

TAXTABLE2.jpg

Thanks!

[etaf]

A spreadsheet maybe useful, as in the yellow banner

There is no any consistent pattern in the gaps between the dates nor in the levels of the tax.

It may be difficult for excel to do that as there is NO Pattern,
BUT you could use a lookup, with ALL the dates and then the 1st table used as a reference
Once dates filled you can copy > Paste Special > Value to keep the values and remove the reference table and formula if needed
If as a lookup table then you only need the date of the change
The sort ascending and then you can use a lookup/match with a 1 to find the nearest OR vlookup( TRUE) which will use the nearest lower
Although If it uses your TABLE as a reference

see sample

[Glenn Kennedy]

In A2:
=SEQUENCE(DATE(2020,12,31)-DATE(2014,1,1),,DATE(2014,1,1),1)

create a lookuptable of dates (ascending) and tax rate. In B2, copied down:
=VLOOKUP(A2,J:K,2,TRUE)

[mehmetcik]

Solution 1.

What you asked for.

Enter your Dates in Column A and your Values in Column B. Sort smallest to Largest.

01/01/2014 16
22/03/2014 88
01/03/2015 31
08/08/2016 22
05/09/2017 53
01/01/2018 99
06/02/2019 77
01/03/2019 5
08/07/2019 14
01/01/2020 36
25/05/2020 25


Now in Cell D1 enter the formula =A1
In Cell D2 enter the formula =D1+1
In Cell E1 enter the formula =INDEX(B:B,MATCH(D1,A:A))
Copy E1 and Paste in E2

Copy D2:E2 and fill down to row 2337 until you get to todays date.

01/01/2014 16
02/01/2014 16
03/01/2014 16
04/01/2014 16
05/01/2014 16
06/01/2014 16
07/01/2014 16
08/01/2014 16
09/01/2014 16
10/01/2014 16

'''''''''''''''''''''''''''''''''''

18/03/2014 16 16
19/03/2014 16 16
20/03/2014 16 16
21/03/2014 16 16
22/03/2014 88 88
23/03/2014 88 88
24/03/2014 88 88
25/03/2014 88 88
26/03/2014 88 88

*****************************************************************
Solution 2
As Solution 1 using a Macro

Please Login or Register  to view this content.

*****************************************************************
Solution 3

Enter a Date in A1 this formula will return your tax rate =LOOKUP(A1,{41640,41720,42064,42590,42983,43101,43502,43525,43654,43831,43976},{16,88,31,22,53,99,77,5,14,36,25})