To use VLOOKUP between dates, put each date range’s starting date in the first column, sort those dates from oldest to newest, and use approximate match: =VLOOKUP(E2,$A$2:$C$6,3,TRUE). If E2 contains a date that falls between two start dates, Excel returns the value from the row with the latest start date that is not later than E2.
How VLOOKUP handles dates between boundaries
VLOOKUP does not search for an interval directly. Instead, approximate match treats the first column as a set of lower limits. It finds the largest first-column value that is less than or equal to the lookup value, then returns a value from that row. That behavior makes it useful for date bands when each band begins on a known date and continues until the next band begins.
For example, suppose a rate changes on January 1, March 1, June 1, September 1, and December 1. A lookup date of May 15 falls after March 1 but before June 1, so VLOOKUP selects the March 1 row. The corresponding rate or category is returned. The date ranges do not have to be equal in length.
The fourth VLOOKUP argument controls this behavior. TRUE requests approximate match; FALSE requests exact match. If the fourth argument is omitted, VLOOKUP uses approximate match by default, so it is safer to include TRUE or FALSE explicitly rather than rely on the default.
Set up a start-date lookup table
Keep the start dates in the leftmost column of the lookup range. Put the result you want to return in another column to the right. For example, this table could represent changing service rates:
- A2: January 1, 2026; B2: Standard; C2: 10
- A3: March 1, 2026; B3: Standard; C3: 12
- A4: June 1, 2026; B4: Standard; C4: 15
- A5: September 1, 2026; B5: Standard; C5: 14
- A6: December 1, 2026; B6: Standard; C6: 16
Assume the date to check is in E2 and the value to return is in column C. With start dates in A2:A6 and return values in C2:C6, use =VLOOKUP(E2,$A$2:$C$6,3,TRUE). The dollar signs keep the table range fixed if you copy the formula to other rows. The 3 tells Excel to return the third column of the selected range, which is column C.
If E2 contains May 15, 2026, the result is 12, because March 1 is the latest start date on or before May 15. If E2 contains October 10, the result is 14, from the September 1 row. These results assume the intervals continue until the next listed start date and there are no uncovered gaps.
Sort the first column in ascending order
Approximate-match VLOOKUP relies on the first column being sorted in ascending order, from earliest date to latest date. If the dates are out of order, the result can be incorrect; Excel may not display an error to warn you. Sort the entire table together so that each date remains paired with its associated values. Sorting only column A would scramble the relationships between dates and results.
On Excel for Microsoft 365 on Windows, select a cell in the table and use Data > Sort to sort by the start-date column, choosing Oldest to Newest. On Mac, the equivalent sorting options are available on the Data tab. Excel for the web also supports sorting from its Data tab. Check that all relevant columns are included in the sort selection, especially if Excel asks whether to expand the selection.
When adding a new boundary, insert it in the right chronological position or sort the table again. A date entered in the wrong order can affect approximate lookups even if the new row itself looks correct.
Use real Excel dates, not date-looking text
Excel stores valid dates as serial numbers and displays them using a date format. A cell that looks like a date may instead contain text, particularly if it was imported from a CSV file or created with an inconsistent regional format. Text dates can fail to match numeric date values or sort in an unexpected order.
To check a date, select its cell and look at the formula bar and number format. You can also temporarily apply a General number format: a real date normally displays as a serial number. Changing a cell’s format alone does not convert text into a true date. If imported values are text, convert them to dates before relying on the lookup, using an appropriate import or conversion method for the source data and locale.
Be careful when typing dates such as 03/04/2026: the interpretation can depend on regional settings. Use an unambiguous date entry method or confirm how Excel interpreted the value. The lookup and boundary dates must be comparable Excel date values.
Choose the right interval boundaries
A start-date table describes intervals such as March 1 up to, but not including, June 1. The lookup for May 31 selects the March 1 row; a lookup for June 1 selects the June 1 row. This is often the cleanest way to represent consecutive periods because each boundary belongs to exactly one period.
If your business rules describe periods as inclusive end dates, translate them carefully into start dates or define the transition points consistently. For example, a period ending May 31 can be represented by a following period starting June 1 when the data contains whole calendar dates. Avoid overlapping definitions such as one range ending on June 1 and the next also beginning on June 1 unless you have decided which range should own that date.
Time values can affect the boundary. Excel stores a time as a fraction of a day, so June 1 at 9:00 a.m. is later than June 1 at midnight. If your lookup values include times, decide whether a boundary takes effect at midnight or at a particular time, and include that time in the boundary values when needed. A date-only boundary is effectively midnight at the beginning of that date.
What happens before the first or after the last date
If the lookup date is earlier than the first start date, there is no start date less than or equal to it, so VLOOKUP returns #N/A. Decide whether that is the correct outcome or whether your table should include an earlier starting boundary. Adding an arbitrary old date just to suppress the error can hide a genuine data problem.
If the lookup date is later than the final start date, VLOOKUP returns the last row’s value. That is correct only if the final interval is intended to continue indefinitely. If the schedule has an end date, an approximate lookup by start date alone cannot tell you that the date is beyond the valid period. You need an additional check against the final end date or a lookup design that verifies both boundaries.
Likewise, a gap between intended periods is not automatically detected. The formula will still choose the previous start date and return its value, even if the lookup date lies in a gap where no result should apply. For schedules with gaps, explicitly validate the returned row’s end date as well as its start date, or use a formula that checks both limits.
Exact match and other lookup options
Use FALSE when the date in the lookup cell must be identical to a date in the first column. With dates in A2:A6 and corresponding values in C2:C6, the exact-match formula is =VLOOKUP(E2,$A$2:$C$6,3,FALSE). A date such as May 15 will return #N/A if only the five boundary dates are listed. Exact match is appropriate for a calendar of specific dates, not for finding which interval contains an arbitrary date.
For Microsoft 365 or Excel 2021 and later, XLOOKUP can express the start-date logic without a numeric column index. With start dates in A2:A6, results in C2:C6, and the lookup date in E2, use =XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,, -1). The match mode -1 means exact match or next smaller item, so the start-date list must still be in ascending order for this use. VLOOKUP remains useful when compatibility with older Excel versions matters.
VLOOKUP also requires the return column to be to the right of the first column, and its column number can become hard to maintain if the table changes. XLOOKUP avoids those constraints, but neither function removes the need to define boundaries, validate dates, or account for gaps. For a straightforward start-date schedule, approximate VLOOKUP is often sufficient.
Common errors and practical checks
- Wrong value returned: Confirm that the first column is sorted oldest to newest and that the full table was sorted together.
- #N/A for an early date: Check whether the lookup date precedes the first start date, or whether one of the values is text rather than a real date.
- Unexpected result at a boundary: Verify whether the boundary includes a time component and decide which interval should receive that exact date or time.
- Wrong return column: The column index is counted from the left edge of the selected range, not from worksheet column A. For A:C, column C is index 3.
- Errors after copying: Use absolute references for the lookup table, as in $A$2:$C$6, and check that the lookup-cell reference changes as intended.
Before using the results in a report or workflow, test a date just before a boundary, the boundary itself, and a date just after it. Also test a date before the first boundary and one after the final boundary. Those checks expose most mistakes in range definitions and clarify how the formula handles the edge cases.
Frequently Asked Questions
Can VLOOKUP find a date that falls between two dates?
Yes. Put sorted start dates in the first column and use approximate match with TRUE. VLOOKUP returns the row for the latest start date that is less than or equal to the lookup date.
Should I use TRUE or FALSE for dates between ranges?
Use TRUE for approximate match between start-date boundaries. Use FALSE only when the lookup date must exactly equal a date listed in the first column.
Do dates have to be sorted for VLOOKUP approximate match?
Yes. Sort the first column in ascending chronological order. Unsorted dates can produce an incorrect result without a clear warning.
Why does VLOOKUP return the previous date’s value?
With approximate match, that is the intended behavior: it selects the greatest start date that is no later than the lookup date. If the result seems wrong, check the boundary definitions, date sorting, and whether the dates are stored as real date values.
Can VLOOKUP check a range using start and end dates?
Not by itself. Approximate match on start dates chooses a candidate interval but does not verify its end date. If your schedule has gaps or a final cutoff, add a separate end-date check or use a formula design that validates both limits.
Related
- Excel Meal Planning Template: A Practical Guide
- Excel MAXIFS: Find the Highest Value for Each Date
- Excel Tabs: How to Find Duplicate Names Across Sheets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filter Shortcut: Turn Filters On and Use ThemIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off for the current…
- Excel INDEX MATCH No Duplicates: How to Return Only Unique MatchesThe short answer: standard INDEX MATCH returns the first match it finds, so to exclude duplicates you must…
- Excel Filter Not Showing All Data? Find the Cause FastExcel filters usually miss rows because the filter range stops before the full dataset, or because the values…
- VLOOKUP in Excel: Practical Tips for Reliable ResultsFor a reliable VLOOKUP in Excel, put the value you want to find in the first column of…
- BMI Calculator Excel Formula: Steps, Examples, and TipsTo calculate BMI in Excel, divide weight in kilograms by height in meters squared. If weight is in…
- How to Forecast Dates in Excel with Useful FormulasTo extend a regular date schedule, add the interval to the previous date—for example, with a date in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.