How To Use VLOOKUP Using Date, To Find Corresponding Holiday With Additional Criteria

[ColtonYYZ]

Hello!

I am looking for a way to do the following:

1. Use a given date in Sheet1
2. Go to Sheet2 and lookup the exact date from Sheet1 in table1, column1
3. If the date isn't listed in the table, end. (aka "")
4. Using date from Column 2, go back to Sheet1
5. If there is data present for the day before and the day after, display a checkmark box (I've already created cell reference for this which is located on Sheet2)
6. If there is data missing on either side of date, end. (aka no checkmark box will be displayed)

Here's what I am thinking so far: =IF(VLOOKUP(U6,Sheet2!$AQ$4:$AS$53,1,FALSE)="XXXX",Sheet2!$AO$3,Sheet2!$AO$4)

**The cell references are my actual references. In order to help me, feel free to change them if it makes it easier for you. The XXXX part is where I am stumped. Essentially the returned value (date if there is one), is then needed to look up back on Sheet1 as a point of reference to see if both adjacent cells have data (time) in them, in order to display the checkmark box in Cell Q15.

I've attached an illustration with a red arrow to show what I would like to appear if the conditions are met; as described above.

Any better way of doing this? This sounds pretty logical to me but if someone has a better, easier, foolproof way to do this, I'm all ears!

Sheet1

							Week Ending:​	25-Feb-2023
	Date	Start	Break	Lunch	Break	End	Holiday Pay
Sun	17-Feb	9:00	10:00	11:00	12:00	13:00
Mon	18-Feb	10:00	11:00	12:00	13:00	14:00	Checkmark Box
Tue	19-Feb	11:00	12:00	13:00	14:00	15:00
Wed	20-Feb	12:00	13:00	14:00	15:00	16:00
Thu	21-Feb	13:00	14:00	15:00	16:00	17:00
Fri	22-Feb	14:00	15:00	16:00	17:00	18:00
Sat	23-Feb	15:00	16:00	17:00	18:00	19:00

Sheet2

Week Ending		Holiday		Checkmark Box Checked
31-Dec-2022	25-Dec	Christmas Day		Checkmark Box Unchecked
7-Jan-2023	1-Jan	New Year’s Day
25-Feb-2023	20-Feb	Family Day
8-Apr-2023	7-Apr	Good Friday
15-Apr-2023	10-Apr	Easter Monday
27-May-2023	22-May	Victoria Day
1-Jul-2023	1-Jul	Canada Day
12-Aug-2023	7-Aug	Civic Holiday
9-Sep-2023	4-Sep	Labour Day
14-Oct-2023	9-Oct	Thanksgiving
11-Nov-2023	11-Nov	Remembrance Day
30-Dec-2023	25-Dec	Christmas Day

 

[ExceLoki]

according to your explanation you need a match on the date and a value for time to be present to show as a holiday. with that the below should work for you.
i did change the cell references and for simplicity i used "holiday" instead of the boxes you are using.
--------------------

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1							Week Ending:	25-Feb-23		Week Ending		Holiday
2										12/31/2022	12/25/2022	Christmas Day
3	Day	Date	Start	Break	Lunch	Break	End	Holiday Pay		1/7/2023	1/1/2022	New Year’s Day
4	Friday	11/11/2022	9:00	10:00	11:00	12:00	13:00	Holiday		2/25/2023	2/20/2022	Family Day
5	Friday	11/18/2022	10:00	11:00	12:00	13:00	14:00			4/8/2023	4/7/2022	Good Friday
6	Friday	11/25/2022	11:00	12:00	13:00	14:00	15:00			4/15/2023	4/10/2022	Easter Monday
7	Friday	12/2/2022	12:00	13:00	14:00	15:00	16:00			5/27/2023	5/22/2022	Victoria Day
8	Friday	12/9/2022	13:00	14:00	15:00	16:00	17:00			7/1/2023	7/1/2022	Canada Day
9	Friday	12/16/2022	14:00	15:00	16:00	17:00	18:00			8/12/2023	8/7/2022	Civic Holiday
10	Sunday	12/25/2022	15:00	16:00	17:00	18:00	19:00	Holiday		9/9/2023	9/4/2022	Labour Day
11										10/14/2023	10/9/2022	Thanksgiving
12										11/11/2023	11/11/2022	Remembrance Day
13										12/30/2023	12/25/2022	Christmas Day
Sheet1

Cell Formulas
Range	Formula
H4:H10	H4	=IFNA(IF(AND(IF(MATCH(B4,$K$2:$K$13,0)>0,TRUE,FALSE),IF(ISBLANK(G4),FALSE,TRUE)),"Holiday",""),"")