To use XLOOKUP for a date that falls between a start date and an end date, test both date columns and look for the resulting 1. With start dates in A2:A100, end dates in B2:B100, return values in C2:C100, and the date to check in E2, use =XLOOKUP(1,(E2>=A2:A100)*(E2<=B2:B100),C2:C100,"No match",0). This returns the value from the first row whose interval includes E2.
What “between two dates” means
This kind of lookup is useful when each row describes a date range: an employee’s assignment, a product’s effective price, a contract period, a booking window, or a rate that changes over time. Instead of looking for an exact copy of the date in one column, Excel checks whether the date is at least the row’s start date and no later than its end date.
The formula uses inclusive boundaries: a date equal to the start or end date counts as a match. That is often what people mean by “between,” but it is not the only possible rule. For example, adjacent intervals may be designed so that the end of one period is excluded and the start of the next is included. Decide how boundary dates should be handled before choosing the comparison operators.
In Microsoft 365 for Windows, XLOOKUP is available directly in a worksheet. It is also available in Excel 2021 and Excel 2024. The formula works similarly in supported versions for Mac and Excel for the web. Excel 2016 and Excel 2019 do not include XLOOKUP; a workbook containing it may show an unsupported-function error in those versions.
How the formula works
XLOOKUP normally compares one lookup value with one lookup array. Here, the two comparisons create arrays of TRUE and FALSE values: one checks that E2 is on or after each start date, and the other checks that E2 is on or before each end date. Multiplying the tests converts TRUE to 1 and FALSE to 0. Only a row that passes both tests produces 1, which XLOOKUP searches for.
The arguments in the example are, in order: the value to find (1), the calculated array to search, the values to return, the text to show if there is no match, and the exact-match setting. The final 0 requests an exact match for the calculated 1. XLOOKUP uses exact matching by default, but writing 0 makes the intention explicit. Each range in the calculation and the return range must cover the same rows.
For example, suppose A2 contains January 1, 2026, B2 contains January 31, 2026, and C2 contains the text January rate. If E2 contains January 18, 2026, the tests for that row are both true, so the formula returns January rate. If E2 is February 1, that row fails the end-date test. The date cells must contain real Excel dates, not text that merely looks like a date.
Set up the date-range table
Keep each interval on one row, with its start, end, and associated result in separate columns. A simple layout is:
- Column A: period start date
- Column B: period end date
- Column C: result to return, such as a label, rate, status, or person
- Cell E2: the date being checked
Enter the dates consistently and format the columns as dates so they are easy to inspect. Formatting changes how a value is displayed; it does not turn text into a date. If a date was imported as text, convert it to a genuine date value before relying on comparisons. You can often correct imported dates with Excel’s date-conversion tools or by re-importing the source with the correct regional date settings.
With the stated layout, put the formula in F2, or another empty cell. It will show the matching value or No match. If the formula is copied down for several lookup dates, E2 becomes E3, E4, and so on, while the table ranges should stay fixed. With start dates in A2:A100, end dates in B2:B100, results in C2:C100, and lookup dates in E2:E20, use =XLOOKUP(1,(E2>=$A$2:$A$100)*(E2<=$B$2:$B$100),$C$2:$C$100,"No match",0) in F2 and copy it down. The dollar signs keep the source table ranges from shifting.
Choose inclusive or exclusive endpoints
The basic formula treats both endpoints as part of the period. If the end date should not count, change the second comparison from less than or equal to less than. For a half-open interval that includes the start but excludes the end, use this version. With start dates in A2:A100, end dates in B2:B100, results in C2:C100, and the tested date in E2, enter =XLOOKUP(1,(E2>=A2:A100)*(E2<B2:B100),C2:C100,"No match",0).
Half-open intervals are useful for consecutive periods. For instance, one period can run from January 1 up to February 1, and the next from February 1 up to March 1. February 1 then belongs to the second interval only. By contrast, if both intervals include their endpoints, a date at the shared boundary can match twice; XLOOKUP will return only one of those rows.
When the end date is a date-only value but the data being checked includes times, a date entered at midnight can exclude later times on that same day. If the intention is to include every time on the end date, compare against the start of the following day using a strict less-than test. With date-only end dates in B2:B100 and a date-time value in E2, use =XLOOKUP(1,(E2>=A2:A100)*(E2<B2:B100+1),C2:C100,"No match",0). For an end value that already contains a meaningful time, use the actual inclusive or exclusive time boundary instead; adding one would extend the interval by a full day.
Overlapping ranges and multiple results
XLOOKUP returns one match, not every matching interval. If two or more rows cover the lookup date, it returns the first matching row in the search order. The first row means the earliest one in the array as currently arranged, not necessarily the row with the most recent start date or the narrowest interval. If priority matters, sort or arrange the table to reflect that rule, or use a method that explicitly filters all matches.
For ranges that should never overlap, treat multiple matches as a data-quality issue. Check for accidental duplicate periods, incorrect end dates, or gaps and overlaps introduced when periods were updated. A formula that quietly returns the first match can conceal these problems, so checking the table’s interval logic is important when the result affects pay, pricing, eligibility, or reporting.
The return array can contain more than one column. With the same date-range columns in A2:B100, related details in C2:D100, and the tested date in E2, use =XLOOKUP(1,(E2>=A2:A100)*(E2<=B2:B100),C2:D100,"No match",0). In Microsoft 365, the returned row can spill into adjacent empty cells. Make sure those cells are clear; otherwise Excel may display a spill error. This is still a single-match lookup, even though it returns several fields.
Common problems and checks
- No match when a date looks correct: Check whether the lookup value and both boundary columns are real date values. Imported text dates, hidden times, or inconsistent regional date interpretation can make apparently identical dates compare differently.
- A result from the wrong period: Inspect for overlapping ranges. XLOOKUP returns the first qualifying row, so the table’s order determines which match wins.
- Unexpected inclusion at an endpoint: Confirm whether the end boundary is inclusive. Replace the less-than-or-equal test with less than when the end must be excluded.
- A formula error: Check that all ranges have equal height, the parentheses are balanced, and the formula uses commas as argument separators for the workbook’s regional settings. Some installations use semicolons instead.
- An unsupported-function message: Verify that the workbook is open in Microsoft 365, Excel 2021, Excel 2024, or another version that supports XLOOKUP. Older Excel versions cannot calculate it natively.
Excel stores genuine dates as serial numbers and compares those numbers. A date-time value also includes a fractional part for the time, which explains why a value at 3 p.m. is later than the same date at midnight. Applying a date display format can hide that time, so if a boundary behaves unexpectedly, inspect the underlying values rather than relying only on what the cells display.
Alternatives and when to use them
XLOOKUP is a clear choice when each row has its own start and end columns and you need one related result. It is easier to read than older lookup workarounds, and it can return values to the left or right of the criteria columns. If you need all matching rows rather than the first, Excel’s FILTER function is a better fit in Microsoft 365 or Excel 2021 and later. If you need to summarize numeric values across a date period, a conditional-sum function such as SUMIFS may be more appropriate than returning a single row.
VLOOKUP and approximate-match lookups are not direct substitutes for this two-boundary test. They typically rely on one sorted lookup column and do not by themselves verify that a date also falls before a separate end date. Older Excel versions without XLOOKUP can use other formulas or helper columns, but the best replacement depends on the workbook version and whether ranges can overlap. Do not assume an approximate match checks both boundaries; it does not.
Frequently Asked Questions
Can XLOOKUP find a date between a start date and an end date?
Yes. Compare the lookup date with both boundary columns, multiply the Boolean tests, and search for 1. The formula in the opening paragraph returns the first row where both conditions are true.
Does XLOOKUP include the start and end dates?
It does if the formula uses greater than or equal to for the start and less than or equal to for the end. Change the end comparison to less than if the end date should be excluded.
What if the date matches more than one range?
XLOOKUP returns the first qualifying row in its search order. Review the intervals for overlap and decide which row should take priority; if every match is needed, use a filtering approach instead.
Can XLOOKUP return a value for a date and time?
Yes. Excel compares date-time serial values, including the time fraction. Make sure the end boundary is set to include the intended times; for a date-only end that should cover the whole day, a strict less-than comparison against the next day is commonly used.
Why does the formula return No match for a date that appears to be in range?
The cells may contain text rather than date values, the interval endpoints may exclude the tested value, or a hidden time component may put it outside the boundary. Also confirm that the start, end, and result ranges have matching dimensions.
Does this formula work in Excel 2019?
No. XLOOKUP is not available as a native function in Excel 2019 or Excel 2016. Use a compatible alternative for those versions, or open the workbook in Microsoft 365, Excel 2021, or Excel 2024.
Related
- Excel Text Filters: How to Find Matching Rows Easily
- Excel Text Box Wrap Text: How to Enable It
- Excel Template for a Social Media Calendar
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Not Null Formula: Check for Nonblank Cell ValuesExcel has no built-in ISNOTNULL or NOTNULL function. To test whether A2 is not empty, use =A2<>""; it…
- Excel Right-Click Shortcut: Open the Menu by KeyboardIn Excel for Microsoft 365 on Windows, press Shift+F10 to open the context menu—the menu you normally see…
- Excel Filter Shortcut Key: Apply, Toggle, and Use FiltersIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off for the selected…
- Sort Excel Data by Date: Steps and TroubleshootingTo sort Excel data by date, select the entire table or the date column within it, then choose…
- Transpose in Excel and Keep Formulas: A Practical GuideTo transpose a range in Excel and keep its formulas, copy the cells, select a blank destination, then…
- HLOOKUP Examples in Excel: Formulas and Common FixesIn Excel, HLOOKUP searches across the first row of a range and returns a value from a specified…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.