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:

Country	New Year's Day	Epiphany	Good Friday	Easter Monday	Labor Day
DEU	2018-01-01	2018-01-06		2018-04-02	2018-05-01
AUT	2018-01-01	2018-01-06		2018-04-02	2018-05-01
CHE	2018-01-01	2018-01-06		2018-04-02	2018-05-01
ESP	2018-01-01	2018-01-06	2018-03-30	2018-04-02	2018-05-01
PRT	2018-01-01	2018-01-06	2018-03-30	2018-04-02	2018-05-01

<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[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))