Excel Formula to Find Date in Multiple Date Ranges

You can use an Excel formula to check where multiple dates listed in one column, are found between a beginning and ending date range in a separate table, and pull the associated value from a third column that lines up with the date range. See this example:


=LOOKUP(2,1/(G:G<=A2)/(H:H>=A2),F:F)


In the formula, the date you want to search for is in A2. This date must fall between the date range which begins in column G and ends in column H. The value to be associated with the date range is given in column F. So when we search for 7/1/2020, we find that it falls between 4/5/2020 and 9/22/2020, which is the date range during which Model D was manufactured.