Excel VLOOKUP Based on Date Range: How To

Formulas & functions · Updated November 22, 2026

To look up a value based on a date range in Excel, you cannot use VLOOKUP directly because it only finds exact matches or the closest smaller value in the first column. Instead, use VLOOKUP with an approximate match (set the last argument to TRUE) on a sorted list of start dates, or use INDEX/MATCH with an array formula. For example, if your date ranges are in column A (start dates) and the corresponding values in column B, the formula =VLOOKUP(D2,$A$2:$B$10,2,TRUE) returns the value for the range that contains the date in D2, provided the start dates are sorted ascending.

How VLOOKUP Handles Dates

In Excel, dates are stored as serial numbers (e.g., 1/1/2024 is 45292). VLOOKUP compares these numbers. With TRUE (approximate match), it scans the first column for the largest value that is less than or equal to the lookup value. This makes it perfect for date ranges if you set up your table with the start date of each range in the first column, sorted from oldest to newest. The ranges are implicitly defined: each row’s start date begins a new range, and the range continues until the next row’s start date.

Setting Up Your Data for VLOOKUP

To use VLOOKUP with date ranges, your lookup table must have the start dates in the first column, sorted in ascending order. For example, to assign a discount tier based on purchase date:

  • Column A: Start date of the tier (e.g., 1/1/2024, 4/1/2024, 7/1/2024)
  • Column B: Tier name (e.g., “Q1”, “Q2”, “Q3”)
  • Column C: Discount rate (e.g., 5%, 10%, 15%)

Then, with a date in cell E2, use =VLOOKUP(E2,$A$2:$C$4,3,TRUE) to return the discount rate. The formula finds the last start date that is less than or equal to E2, so any date between 4/1/2024 and 6/30/2024 returns the Q2 discount.

Common Pitfalls and Fixes

The biggest mistake is forgetting to sort the first column. If the start dates are not in ascending order, VLOOKUP with TRUE returns incorrect or #N/A results. Another issue is using FALSE (exact match) – that only works if the lookup date exactly equals a start date, which is rarely what you want. Also, beware of dates stored as text: if your lookup date is text, VLOOKUP won’t recognize it as a date. Use DATEVALUE() or convert to a real date. Finally, if your ranges have gaps (e.g., no coverage for certain dates), VLOOKUP will still return a value from the previous range, so ensure your ranges are contiguous.

Alternatives: INDEX/MATCH and XLOOKUP

While VLOOKUP works, INDEX/MATCH is more flexible because it can look up to the left or right. For a date range, you can use an array formula: =INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10<=E2)*($B$2:$B$10>=E2),0)) (enter with Ctrl+Shift+Enter in older Excel, or with dynamic arrays in Excel 365). This checks both start and end dates. In Excel 365, XLOOKUP is simpler: =XLOOKUP(E2,$A$2:$A$10,$C$2:$C$10,,-1) uses the next smaller match. However, XLOOKUP still needs sorted data for the -1 match type.

Working with End Dates Explicitly

If your data includes both start and end dates in separate columns, VLOOKUP alone cannot handle two criteria. You’ll need an array formula or a helper column. For example, create a helper column that concatenates the start and end dates, then use MATCH with INDEX. Or, use SUMPRODUCT: =SUMPRODUCT(($A$2:$A$10<=E2)*($B$2:$B$10>=E2)*$C$2:$C$10) – this returns the value from column C where the date falls between the two dates. This works without sorting and handles non-contiguous ranges.

Frequently Asked Questions

Why does my VLOOKUP return #N/A even though the date is in the range?
Most likely your first column is not sorted ascending, or your lookup date is stored as text. Check the cell format and use DATEVALUE() if needed. Also, ensure the range reference is correct (e.g., $A$2:$B$10).

Can I use VLOOKUP with a date range that has no start date?
No, VLOOKUP requires a first column to compare against. If you only have end dates, you’d need to invert your logic or use an array formula. Consider using INDEX/MATCH with both start and end columns.

Does VLOOKUP work with dates in the future?
Yes, as long as the serial numbers are valid. Excel handles dates from 1900 onward. Just ensure your lookup date is a real date, not text.

What’s the difference between TRUE and FALSE in VLOOKUP?
TRUE (or omitted) does an approximate match, returning the closest match less than or equal to the lookup value. FALSE does an exact match only. For date ranges, you need TRUE, but the first column must be sorted.

Is VLOOKUP the best function for date ranges?
Not necessarily. XLOOKUP (Excel 365) is more intuitive, and INDEX/MATCH with array formulas is more flexible for two-sided ranges. VLOOKUP is fine for simple, sorted start-date ranges.

Performance and Large Data Sets

VLOOKUP with approximate match is fast, but if you have thousands of rows and many lookups, consider using XLOOKUP or INDEX/MATCH – they are generally faster and less error-prone. Also, avoid volatile functions like OFFSET if you can. For dynamic arrays, Excel 365’s FILTER can extract all matching rows: =FILTER(C2:C10,(A2:A10<=E2)*(B2:B10>=E2)) returns all values that meet the condition.

Remember to always test your formula with a few known dates to verify the results. If you’re using this in a financial model, double-check that your date ranges are correctly defined and that there are no overlapping ranges, which can cause ambiguous results.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.