Excel formula - to look up each country of holidays in a range of dates

[Kong Kong]

This is my ever first post forum, really excited!
I have basic knowledge of excel formula, hence, need some help here.


I have a range of dates (Start date, End date) and countries code on Sheet 1. Sheet 2, with all the listed holidays in all the countries.


How am I gonna match the holiday in each country from the range of dates? This is to exclude the holidays from my calculation.


Thanks in advance for the help.


Sheet 1:
Country Start date End Date
PRT 2018-03-30 2018-04-04
PRT 2018-03-30 2018-04-04
GBR 2018-03-30 2018-04-04
AUT 2018-03-30 2018-04-04


Sheet 2:
[TABLE="width: 584"]
<colgroup><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Country[/TD]
[TD]New Year's Day[/TD]
[TD]Epiphany[/TD]
[TD]Good Friday[/TD]
[TD]Easter Monday[/TD]
[TD]Labor Day[/TD]
[/TR]
[TR]
[TD]DEU[/TD]
[TD]2018-01-01[/TD]
[TD]2018-01-06[/TD]
[TD] [/TD]
[TD]2018-04-02[/TD]
[TD]2018-05-01[/TD]
[/TR]
[TR]
[TD]AUT[/TD]
[TD]2018-01-01[/TD]
[TD]2018-01-06[/TD]
[TD] [/TD]
[TD]2018-04-02[/TD]
[TD]2018-05-01[/TD]
[/TR]
[TR]
[TD]CHE[/TD]
[TD]2018-01-01[/TD]
[TD]2018-01-06[/TD]
[TD] [/TD]
[TD]2018-04-02[/TD]
[TD]2018-05-01[/TD]
[/TR]
[TR]
[TD]ESP[/TD]
[TD]2018-01-01[/TD]
[TD]2018-01-06[/TD]
[TD]2018-03-30[/TD]
[TD]2018-04-02[/TD]
[TD]2018-05-01[/TD]
[/TR]
[TR]
[TD]PRT[/TD]
[TD]2018-01-01[/TD]
[TD]2018-01-06[/TD]
[TD]2018-03-30[/TD]
[TD]2018-04-02[/TD]
[TD]2018-05-01[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Welcome to the board.

Try this on Sheet1 in D2

=NETWORKDAYS(B2,C2,INDEX(Sheet2!$B$2:$F$6,MATCH(A2,Sheet2!$A$2:$A$6,0),0))