You can use the following formula with the VLOOKUP function in Excel to look up a value that falls in a date range and return the corresponding value in another range:
=VLOOKUP(E2,$A$2:$C$7,3,TRUE)
This particular formula looks up the date in cell E2 within the range A2:C7 and returns the corresponding value from the third column in the range.
Note that this formula assumes column A contains a list of start dates, column B contains a list of end dates, and column C contains some value you’d like to return.
The following example shows how to use this formula in practice.
Example: How to Use VLOOKUP with Date Range in Excel
Suppose we have the following dataset in Excel that shows the start and end dates for when various people were CEO of a particular company:
Suppose we want to know who the CEO was on 4/9/2007.
We can type this date into cell E2 and then type the following formula into cell F2:
=VLOOKUP(E2,$A$2:$C$7,3,TRUE)
The following screenshot shows how to use this formula in practice:
The formula correctly returns Bob, which is the person who was the CEO on 4/9/2007.
If we change the date in cell E2 to 12/15/2020, then the formula will automatically update to return the name of the person who was CEO on this date:
The formula correctly returns Eric, which is the person who was the CEO on 12/15/2020.
Note that the start dates in column A are already sorted from earliest to latest, which is why the VLOOKUP function was able to return the correct value from column C.
If the first column in your lookup range is not sorted, the VLOOKUP function could return unexpected results.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: Use VLOOKUP to Return Multiple Values Horizontally
Excel: Use VLOOKUP to Return Blank Instead of Zero
Excel: How to Use VLOOKUP by Exact Date
