Forum Discussion
Solved
How To Use VLOOKUP Using Date, To Find Corresponding Holiday With Additional Criteria
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 l...
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)
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?
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?
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?
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)
- Thanks so much! I didn't know you could use 2 equal signs like you've done!
Thank you, again!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.