If you’ve ever needed to pull a value based on a date that falls between a start and end date, you know standard lookup functions struggle. This article covers ten concrete ways to use XLOOKUP with date ranges in Excel, from finding the active price for a given day to handling overlapping periods. It’s written for analysts, accountants, and anyone who manages schedules or pricing tiers in spreadsheets.
Exact Date Match with XLOOKUP
The simplest use is looking up a value on a specific date. Use =XLOOKUP(date, date_column, value_column) to find an exact match. This works when your lookup date appears exactly once in the date column. If you have duplicate dates, XLOOKUP returns the first match, so sort your data or use a helper column to make dates unique.
Find the Last Date Before a Given Date
To find the most recent date that is less than or equal to your lookup date, set the match mode to -1 (exact match or next smaller). For example, =XLOOKUP(A2, dates, values, , -1) returns the value for the last date on or before A2. This is perfect for finding the applicable rate or price as of a certain day. Ensure your date column is sorted ascending, or the result will be unpredictable.
Find the First Date After a Given Date
Conversely, use match mode 1 to find the first date that is greater than or equal to your lookup date. This is useful for upcoming deadlines or the next scheduled event. The formula =XLOOKUP(A2, dates, values, , 1) works only on sorted data. If you have unsorted dates, consider using MINIFS or an array formula instead.
Approximate Match for Date Ranges (Tiered Pricing)
When your date ranges are contiguous and non-overlapping, like tiered pricing that changes on specific dates, you can use XLOOKUP with match mode -1 on the start dates. For example, if you have a table with start dates and prices, =XLOOKUP(lookup_date, start_dates, prices, , -1) returns the price that was active on that date. This eliminates the need for nested IF statements.
Using XLOOKUP with Multiple Criteria (Start and End Date)
For non-contiguous ranges, combine XLOOKUP with boolean logic. Use =XLOOKUP(1, (start_col <= lookup_date) * (end_col >= lookup_date), value_col). The multiplication creates an array of 1s and 0s where 1 means the date falls within the range. XLOOKUP then returns the corresponding value from the first matching row. This works without sorting and handles overlapping ranges by returning the first match in the order of your data.
Dynamic Array with SEQUENCE for a Date Range
If you need to return values for multiple dates at once, use SEQUENCE to generate a list of dates and pass it to XLOOKUP. For example, =XLOOKUP(SEQUENCE(10,1,start_date,1), dates, values, , -1) returns ten values for ten consecutive days. This is powerful for building dynamic dashboards where the date range is controlled by a spinner or a cell reference. The result spills into adjacent cells automatically.
Handling Missing Dates with a Default Value
When your lookup date is not in the list and you don’t want an error, use the if_not_found argument. For instance, =XLOOKUP(date, dates, values, "Not found", -1) returns “Not found” if the date is earlier than the first date in your list. This is especially helpful when using match mode -1 because dates before the first entry would otherwise return #N/A.
Returning Multiple Columns from a Date Range
XLOOKUP can return an entire row of values by specifying a return array with multiple columns. For example, =XLOOKUP(date, dates, B:D, , -1) returns three values: one from column B, one from C, and one from D. This is useful when you have a table with start dates and multiple attributes (e.g., price, discount, tax rate). The formula spills horizontally.
Nested XLOOKUP for Two-Dimensional Date Ranges
When you have a matrix of dates and categories, nest two XLOOKUPs. For example, =XLOOKUP(category, categories, XLOOKUP(date, dates, data_matrix, , -1), , 0) first finds the row for the date, then picks the column for the category. This is more readable than INDEX/MATCH with multiple criteria. Ensure your date axis is sorted ascending for the inner lookup to work correctly.
XLOOKUP with Boolean Logic for Overlapping Ranges
When date ranges overlap, the simple boolean approach returns the first match. If you need the most recent or the longest range, sort your data accordingly. For example, sort by start date descending so the newest range appears first, then use =XLOOKUP(1, (start_col <= A2) * (end_col >= A2), value_col). This returns the value from the most recent overlapping range, which is often the desired behavior for subscriptions or memberships.
How to Choose
Deciding which method to use depends on your data structure. If your ranges are contiguous and sorted by start date, use match mode -1 on the start column — it’s the fastest and simplest. If ranges overlap or are unsorted, use the boolean multiplication approach with XLOOKUP(1, ...). For returning multiple values, use the multi-column return array. Always sort your data when using approximate match modes, and test with edge cases like dates before the first range or after the last.
Frequently Asked Questions
Q: Does XLOOKUP work with unsorted dates?
A: Yes, but only if you use match mode 0 (exact match) or the boolean logic approach. Approximate matches (-1 or 1) require sorted data.
Q: Can XLOOKUP return multiple values for a date range?
A: Yes, by specifying a return array with multiple columns. It returns one row of values, not multiple rows. For multiple rows, use FILTER instead.
Q: What if my lookup date falls between two ranges but not on a start date?
A: With match mode -1, XLOOKUP returns the value for the most recent start date that is less than or equal to your lookup date, which effectively handles the range. For non-contiguous ranges, use the boolean method.
Q: How do I handle a date that is earlier than my first range?
A: Use the if_not_found argument to return a custom message like “Before first date”. Without it, XLOOKUP returns #N/A.
Q: Is XLOOKUP better than INDEX/MATCH for date ranges?
A: For most cases, yes — it’s shorter and easier to read. INDEX/MATCH is still useful when you need to perform a two-way lookup without spilling, or when you’re working in older Excel versions that don’t support XLOOKUP.
Final Thoughts
XLOOKUP is a versatile tool for date range lookups, but the key is understanding how match modes and boolean logic work. Start with the simple approximate match for sorted, contiguous ranges, and graduate to the boolean approach for complex, overlapping data. With these ten techniques, you can handle almost any date-based lookup scenario in Excel.