To find the value tied to the most recent start date on or before a date, use XLOOKUP with match mode -1. With ascending start dates in A2:A5, results in C2:C5, and the date to check in E2, enter =XLOOKUP(E2,A2:A5,C2:C5,"No prior start date",-1); for a lookup date of June 15 and starts on January 1, April 1, July 1, and October 1, it returns the result on the April 1 row. If your records have separate start and end dates, use a two-condition XLOOKUP instead.
What “between dates” can mean
There are two common date-lookup tasks, and the right formula depends on which one describes your data. In a start-date schedule, each row takes effect from its start date until the next row begins. You want the row with the latest start date that is not later than your lookup date. This is a good fit for XLOOKUP’s “exact match or next smaller item” option.
In a bounded-range table, each row explicitly has both a start date and an end date, such as a promotion valid from April 1 through June 30. Here, the lookup date must meet both conditions: it is on or after the start and on or before the end. A conditional-array XLOOKUP checks both at once.
These methods are not interchangeable in every workbook. The first assumes that each start date remains in effect until the next start date. The second can represent gaps, irregular periods, and separate end dates. Decide how your dates define a match before choosing a formula.
Use the nearest earlier start date
For the first approach, put each period’s start date in column A and its return value in column C. Put the lookup date in E2. With dates sorted from oldest to newest, use =XLOOKUP(E2,A2:A5,C2:C5,"No prior start date",-1). In this formula, -1 is the match_mode argument: it asks for an exact match, or otherwise the next smaller value. For June 15, the April 1 row is the closest start date that is not after June 15, so its result is returned.
This is useful for effective-dated information such as tax rates, price schedules, commission tiers, exchange-rate records, or a department assignment that changes on known dates. For example, if a rate beginning April 1 applies until another rate begins July 1, a June lookup returns the April rate without needing to store June 30 as an end date.
- Keep the start-date lookup range and return range the same size and aligned row by row.
- Sort the start dates in ascending chronological order when using match mode -1.
- Include a start date that covers the earliest possible lookup date, or decide what the “no prior start date” result should be.
XLOOKUP uses exact match by default, so leaving out the final argument would return a result only when E2 exactly matches a date in column A. The -1 match mode is what enables the “between starts” behavior. Do not confuse it with search_mode -1, which means search from last to first; that is a different argument with a different purpose.
Match a date inside explicit start and end dates
For a table with start dates in A2:A10, end dates in B2:B10, return values in C2:C10, and a lookup date in E2, use =XLOOKUP(1,(E2>=A2:A10)*(E2<=B2:B10),C2:C10,"Not found"). The comparisons produce TRUE or FALSE values for each row. Multiplying the two tests makes a 1 where both are true and a 0 where either condition fails; XLOOKUP searches for 1 and returns the aligned value. If June 15 falls in the row whose dates are April 1 and June 30, the result is the value in that row’s column C.
The end-date comparison shown is inclusive: a lookup on the end date itself counts as inside the period. That is often the intended rule for date-only records. Check the workbook’s business rule, though. If one period ends exactly when the next begins, inclusive ranges overlap on that boundary, which means the formula can find more than one matching row. XLOOKUP returns the first match in its search direction, so an accidental overlap can hide a data problem rather than report it.
For adjacent periods where the next start marks the instant the previous period stops, a common convention is to use an inclusive start and an exclusive end: start date ≤ lookup date < next start date. With starts in A2:A10, exclusive boundary dates in B2:B10, values in C2:C10, and the lookup in E2, use =XLOOKUP(1,(E2>=A2:A10)*(E2<B2:B10),C2:C10,"Not found"). Under this convention, if one period ends at the boundary where the next begins, that boundary belongs only to the new period. Make the table’s boundary meaning explicit to anyone maintaining it.
How Excel treats dates and times
Excel stores ordinary dates as serial numbers, which lets it compare dates numerically. A cell that looks like “June 15” is therefore still a number underneath, displayed with a date format. The lookup works only as intended when the lookup value and the table boundaries are real Excel dates, not text strings that merely resemble dates. Formatting a text value as a date does not convert the underlying text into a date.
Times matter because a date without a time represents midnight at the start of that day. Suppose an end date is June 30 stored as June 30 at 12:00 AM. A timestamp of June 30 at 3:00 PM is later than that value, so the inclusive test against that end date will not match. For records that include times, consider using the next day’s midnight as an exclusive boundary and test with “less than,” or store precise start and end timestamps that reflect the actual rule.
To check whether a cell is a real date, select it and inspect the formula bar or temporarily change its number format. A serial number displayed as a date is normal. If imported values are text, convert them to actual dates before relying on approximate comparisons or date-range tests; the right conversion method depends on the source and its regional date format.
Sort order, gaps, and overlapping ranges
Approximate matching depends on ordering. For match mode -1, sort the start-date list ascending and avoid blanks or text mixed into the lookup range. If the dates are unsorted, an approximate result may be incorrect. A formula using explicit start and end conditions does not require the rows to be sorted to find a matching row, but sorting still makes the table easier to inspect and maintain.
With start-date-only logic, any lookup date after the earliest start date will match the latest earlier start, even if your business rules intended a gap with no active value. If gaps are possible, use explicit end dates or add a separate check that confirms the selected period is still valid. With explicit ranges, a gap naturally returns the not-found value. Overlaps can return the first matching row, so review the source table for duplicate or intersecting periods when the result is ambiguous.
When using the explicit-range formula, the lookup ranges must have matching dimensions. A2:A10, B2:B10, and C2:C10 all cover nine rows. If one range is shorter or shifted, Excel can return an error or pair a date with the wrong result. For larger tables, converting the data to an Excel table can make the layout more readable; structured references can also adjust as table rows are added, provided the columns remain aligned.
Choose the method that fits the table
- Choose the nearest earlier start date when each new start automatically replaces the previous entry and there are no gaps that need special handling.
- Choose explicit start and end tests when periods have defined boundaries, gaps are meaningful, or the end date can vary independently.
- Choose a different approach if the match depends on multiple identifiers as well as dates, such as a customer ID and an effective date. You can combine criteria into the lookup array, but verify that each matching record is unique.
For example, suppose a rate table has customer IDs in A2:A100, effective dates in B2:B100, and rates in C2:C100. A date-only lookup could select the wrong customer’s rate even if the date logic is correct. The lookup must account for both customer and date, and the source data should be organized so the intended effective record is identifiable. Date matching solves the time condition, not every other condition in the dataset.
XLOOKUP compared with older lookup methods
XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Compared with VLOOKUP, it can return values from columns on either side of the lookup column and specifies the lookup and return ranges separately. It also offers explicit match modes, including exact-or-next-smaller, which is convenient for start-date schedules. These advantages do not remove the need to sort data correctly for approximate matching.
Older Excel versions that do not include XLOOKUP may use INDEX and MATCH for similar tasks, but the exact formula depends on whether the data is a sorted start-date list or a set of bounded ranges. If a workbook must be opened in an older version, check compatibility before distributing it: an unsupported function may display as a name error rather than calculate. Avoid replacing XLOOKUP with VLOOKUP solely because it is familiar if the lookup column and return column are not arranged appropriately.
Common errors and practical checks
- Wrong period returned: confirm the start dates are genuine dates, sorted ascending, and paired with the intended return rows.
- Not found unexpectedly: check whether the lookup date is earlier than every start date, whether the input is text, and whether the explicit ranges actually contain the lookup date.
- Unexpected match on a boundary: verify whether the end is inclusive or exclusive and whether adjacent ranges overlap.
- Timestamp misses an end date: remember that a date-only end value is midnight; use an appropriate timestamp boundary or exclusive next-day boundary.
- Spill or calculation issues: confirm that the Excel version supports XLOOKUP and dynamic-array calculations, and that the formula ranges have compatible sizes.
For a dependable result, test a date that exactly equals a start, one that falls between starts, a date on an end boundary, and a date outside all valid periods. These checks expose the most common disagreements about inclusivity and gaps. If the schedule is used for important reporting, retain a visible not-found result rather than silently returning a blank; an explicit message makes missing coverage easier to detect.
Frequently Asked Questions
Can XLOOKUP find a date between two dates?
Yes. Use match mode -1 to find the latest start date on or before the lookup date, or use conditions that test whether the date is at least the start and no later than the end. Pick the method that matches how your table defines each period.
Does XLOOKUP require dates to be sorted?
For approximate match mode -1 on a start-date list, sort the dates in ascending order. A formula that tests explicit start and end conditions does not rely on sorted rows, though tidy ordering helps you review the data.
What does -1 mean in an XLOOKUP date formula?
When supplied as match_mode, -1 means exact match or, if there is no exact match, the next smaller item. It is not the same as search_mode -1, which searches from the last item toward the first.
Why does a date-range lookup return “Not found” for the last day?
The end boundary may be stored as midnight while the lookup includes a later time on that same calendar day. Check whether the cells contain times and whether your rule requires an inclusive end or an exclusive next boundary.
Can XLOOKUP return more than one matching date range?
A standard XLOOKUP returns one result: the first match it encounters. If ranges overlap, it does not list every match. Correct the overlaps or use a method designed to return multiple results if multiple matches are genuinely required.
Is XLOOKUP available in every Excel version?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in some older perpetual versions. If a workbook needs to support an older release, use a compatible alternative and verify the formula in that version.
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
- FV Examples in Excel: Formula and Worked ExamplesExcel’s FV function calculates the future value of money invested or saved at a steady interest rate. For…
- Excel Workdays Between Dates: Formulas and ExamplesTo count workdays between two dates in Excel, use NETWORKDAYS. With the start date in A2 and the…
- Excel CONCAT Examples: Formulas for Joining TextTo join a first name in A2 and a surname in B2 with a space between them, enter…
- Outlook Rules: Filter Your Incoming Mail AutomaticallyCreate an Outlook rule by choosing a condition, such as who sent a message or a word in…
- Sort Excel by Color: Steps, Options, and TroubleshootingTo sort an Excel sheet by color in Microsoft 365 for Windows, select a cell in your data,…
- Excel Insert Row and Copy Formulas From Above EasilyTo insert a row and copy formulas from above in Excel for Microsoft 365 on Windows, right-click the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.