title: Excel: Find if cell is >= or <= of an array of dates
body: <p>I have made a dynamic calendar here: <a href="https://docs.google.com/spreadsheets/d/1VoY-6_iUIy-tkIyFJoWpwDJSSaqewkugjG4qEtkwl_8/edit?usp=sharing" rel="nofollow noreferrer">Please copy and paste for testing</a></p>
  
  <p>The calendar currently uses <code>VLOOKUP</code> formulas (and more) to find guests checking into a property and lists information associated with their stay such as number of nights and their contact details. All of this is derived from a list shown to the right. Yes, it does contain</p>
  
  <p>The date can be changed in cell B3 to the first of each month to view new guests checking in each month.</p>
  
  <p>I now need to create conditional formatting formulas to alter the colour of the 3 cells spread across each day. You will see 7th December 2019 has the first cell in green, second white and 3rd red. I have created a formula to detect if  the date (always right most cell) is contained in the range <code>X5:AF13</code>. This formula will not work if the check-in date is any other day than a Saturday. The 'prototype' formulas created so far are illustrated below.</p>
  
  <p><a href="https://i.stack.imgur.com/5iTYS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5iTYS.png" alt="enter image description here"></a></p>
  
  <p>I need to construct a new formula that will work so the date (07, 08, 09 etc) is searched against the occupied days/nights in the range <code>X5:AF13</code> shown in pink. My proposed method is to cross reference (<code>VLOOKUP</code>) the date against the days/nights which the property is occupied for. I need to find the <code>SUM([Date2]+[Nights]-1)</code> to find the check-out date and somehow find if the date shown in the calendar is <code>&gt;=</code> or <code>&lt;=</code> to any of dates between check-on and check-on with the list in pink. </p>
  
  <p>Is <code>VLOOKUP</code> the right forumla or is <code>MATCH</code> or even a combination with <code>IF(AND(</code> and <code>SUM(</code>? Slightly stuck...</p>
  
  <p><a href="https://i.stack.imgur.com/HeUwa.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/HeUwa.png" alt="enter image description here"></a></p>