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

[bryen79]

I am looking for a way to do the following: display a checkmark box (using the corresponding cell reference in Sheet2) if the day is a holiday.

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($B5,Sheet2!$D$3:$F$11,1,FALSE)="XXXX",Sheet2!$B$3,"")

**The XXXX part is where I am stumped. I have tried entering $B5 but I get an error. I don't know how to use the value it's looking up to find it in the table and if found, simply display the checkmark box, otherwise, display nothing.

Essentially the returned value (date if there is one), is then needed to look up on Sheet1 Column B, as a point of reference to see if both adjacent row (above and below) 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!

I've included the spreadsheet here on this forum, for ease of use in hopes it will or can be a simple solution.

Link to another forum I have posted this question:

https://www.mrexcel.com/board/threads/how-to-use-vlookup-using-date-to-find-corresponding-holiday-wi...

[#2]

@bryen79 

The screenshot in your post here is confusing: the table on the left contains dates in 2022, and the one on the right contains dates in 2023.

The example in the MrExcel topic is confusing too: the date next to 25-Feb on Sheet2 is 20-Feb, but you indicate a check mark on Sheet1 for 18-Feb.

What am I missing?

[#3]

Hi I'm sorry! My ADHD is in overtime trying to figure out various ways to display what I need it to. I've since updated my original post with strikethrough and re-uploaded the photo and book.

Since posting, I realized that if the holiday is on a Sunday, the formula can't look at the day before (Sat) because it wouldn't be on this report as it's the week prior. So I've now reduced this down to simply documenting that that day itself is a holiday.

Instead of displaying the checkmark box, would it be possible to highlight the row orange? Then the user can manually be triggered by the colouring to manually review the previous week and current week to see if they qualify for holiday pay?

[#4 · Accepted solution]

@bryen79 

See the attached version. I created a conditional formatting rule of type 'Use a formula to determine which cells to format' with formula

=$B5=VLOOKUP($H$2,Sheet2!$D$3:$E$11,2,FALSE)

[#5]

Thanks so much! I didn't know you could use 2 equal signs like you've done!

Thank you, again!

[#6]

@bryen79 

The first = starts the formula.

The second = is used to compare the cell value with the result of VLOOKUP. If they are equal, the formula returns TRUE, otherwise FALSE.