Conditional formatting on date ranges

[excelnoob20]

Hi there,
I am currently trying to create a dynamic calendar in excel.

A disclaimer upfront: my username checks out. This is literally my second day working with excel formulas. I have done some VBA in Word before but that's it.

So my idea is to mark certain cells in my calendar in color green when a vacation is ongoing (see picture attached)

Here in Germany there are certain ranges of holidays, e.g. from Jan 01 until Jan 06. I would like to mark every value in between these two ones. I am aware on how conditional formatting works, but I am desperate enough with those date values now that I have created an account on this forum here.

What I have:
I have a table where I manually inserted the start and end dates of the holidays into and calculated the duration. (Structure below, date format "DD.MM.YYYY")

Start	End	#Days (End-Start+1)
01.01.2020	06.01.2020	6
06.04.2020	18.04.2020	13
...

Furthermore I have some conditional formatting ongoing for Saturdays and Sundays as well as other public holiday here (marked in yellow, see picture attached)

Now for the problem...
I have absolutely no idea how to create this without VBA. For this project specifically I am not allowed to use vba and I think it must be possible without it.. I have tried a little bit with VLOOKUP and MATCH but have not been successful at all.

Can anyone help out?
Thank you.

 

[excelnoob20]

Okay, so I found something out.

I have learned that VLOOKUP is not feasible for this use case, instead a combination of MATCH and INDEX will be used.

INDEX and MATCH in Excel

Use INDEX and MATCH in Excel and impress your boss. Instead of using VLOOKUP, use INDEX and MATCH. To perform advanced lookups, you'll need INDEX and MATCH.

www.excel-easy.com

My new problem now is that I get a #N/A Error on this formula:

=INDEX(DATA!$L$3:$L$29;MATCH($B$4;DATEVALUE(DATA!$J$3:$J$29);0))

DATA!$L$3:$L$29 - This is the column with the duration (see table above)
$B$4 - This is the cell in my calendar where I get the date from (see picture attached) - in This case it is the row with "01 Mi ...." I fixed this value due to testing, I will update this post when I found a solution.
DATA!$J$3:$J$29 - This is where the start date stands (see table above)

The error occures when I try to match one date value against the array of data. How can I see if this specific date value is IN the array of data?