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
- Excel Strikethrough: How to Apply & Remove It
- Excel Utility for E-Invoice: What It Does
- Excel Undo Encrypt with Password? How to Remove It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Excel: Count Cells That Contain Text in a RangeTo count cells that contain text in Excel, use =COUNTIF(A2:A100,"*"), replacing the range with your own. The asterisk…
- Excel Word Count Formula: Count Words in Cells and TextTo count words in a cell in Excel, use =IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1), replacing A2 with the cell you want…
- Excel Uppercase to Lowercase: Shortcut and FormulaExcel has no dedicated keyboard shortcut that changes selected text from uppercase to lowercase. The dependable method is…
- Excel Text Plus Cell Value: Formulas, Formatting, and FixesTo combine text with a cell value in Excel, join them with the ampersand operator (&). For example,…
- Randomize Cells in Excel: Formulas, Steps, and TipsYes. In Excel for Microsoft 365 on Windows, you can randomize a list with a formula such as…
- How to Filter Out Bold Text in ExcelTo filter rows according to whether a cell is bold, add a helper column that labels each row…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.