I have a column of dates that I want to check against a table of public holidays.
Sheet 1:
A B C
Date
Mon 23/04/2018 Formula
Tue 24/04/2018 Formula
Wed 25/04/2018 Formula
Thu 26/04/2018 Formula
Fri 27/04/2018 Formula
Sat 28/04/2018 Formula
Sun 29/04/2018 Formula
Sheet2:
Day 2017 2018 2019
New Year's Day 01/01/2017 01/01/2018 01/01/2019
New Year's Day 02/01/2017 01/01/2018 01/01/2019
Australia Day 26/01/2017 26/01/2018 28/01/2019
Labour Day 13/03/2017 12/03/2018 11/03/2019
Good Friday* 14/04/2017 30/03/2018 19/04/2019
Easter Sunday 16/04/2017 01/04/2018 21/04/2019
Easter Monday 17/04/2017 02/04/2018 22/04/2019
ANZAC Day* 25/04/2017 25/04/2018 25/04/2019
Queens Bday 12/06/2018 11/06/2018 10/06/2019
AFL Grand Final 29/09/2017 28/09/2018 27/09/2019
Melbourne Cup 07/11/2017 06/11/2018 05/11/2019
Christmas Day 25/12/2017 25/12/2018 25/12/2019
Boxing Day 26/12/2017 26/12/2018 26/12/2019
Boxing Day 26/12/2017 26/12/2018 26/12/2019
I've tried a dozen formulas with no success.
To get the column: MATCH(YEAR(B4),Sheet2!A:D,0)
When i use vlookup it gives an error because the column changes based on the year of the data.
I've tried index/match but I couldn't make that work. I can't figure out how to get a dynamic array to work.
My goal: IF(B4) is found to be in Sheet2 then "PH" else "not PH"
I think it should be a simple solution but none of the solutions I've researched can seem to solve this. I must be using the wrong keywords.
I've searched for index/match but there was nothing and for dynamic array it is all about macros or too complex for my level of excel.
Bookmarks