To use VLOOKUP for a date that falls between a start date and an end date, sort the start dates from oldest to newest and use approximate match, then check that the returned end date still includes the lookup date. If periods overlap or you need a more direct test of both boundaries, use XLOOKUP with a condition for each date limit.
What “between two dates” means in Excel
This question usually means you have a table of date ranges and want Excel to return a value for whichever range contains a chosen date. For example, a price list might apply one price from January 1 through January 31 and a different price from February 1 through February 28. Given February 14, you want the price for the February range—not necessarily a row whose date exactly equals February 14.
That is different from looking up a date that exactly matches a date in a single column. VLOOKUP can do exact-match lookups, and it can do approximate-match lookups, but it does not have a built-in option that says “find the row where this date is greater than or equal to the start and less than or equal to the end.” The layout and match type matter, so the safest method depends on how your periods are organized.
Prepare a date-range table
For the VLOOKUP method, arrange the table with the start date in the first column, the end date in the second, and the value to return in a later column. For example, enter these illustrative periods in A2:C4:
- January 1, 2026 to January 31, 2026; rate 5%.
- February 1, 2026 to February 28, 2026; rate 6%.
- March 1, 2026 to March 31, 2026; rate 6.5%.
Put the date to look up in E2. Make sure the entries in columns A, B, and E are real Excel dates, not text that merely looks like a date. Excel stores dates as serial numbers, which lets it compare them chronologically. A date display such as “Feb 14, 2026” is just formatting; it does not establish that the underlying value is a valid date.
Keep the start dates in ascending order, with the earliest at the top. The periods should not overlap, and each lookup date should belong to no more than one period. If your date ranges have gaps, a date in a gap should not be assigned a value just because it comes after an earlier start date; the end-date check in the formula below prevents that.
Use VLOOKUP with approximate match
With start dates in A2:A4, end dates in B2:B4, rates in C2:C4, and the date to look up in E2, enter this formula in another cell:
=IFERROR(IF(E2<=VLOOKUP(E2,$A$2:$C$4,2,TRUE),VLOOKUP(E2,$A$2:$C$4,3,TRUE),"No matching period"),"No matching period")
For the example table above, if E2 contains February 14, 2026, the formula returns 6%. It first finds the row with the latest start date that is not later than February 14, then checks the end date in that row. Since February 14 is on or before February 28, the rate is returned. If E2 contains April 1, 2026, the formula returns “No matching period” because the date is after the last listed end date.
The third argument, 2, tells the first VLOOKUP to return the end date from the second column of the selected row. The other VLOOKUP uses 3 to return the rate from the third column. The final argument, TRUE, requests approximate match. The IFERROR wrapper handles a lookup date earlier than the first start date, when VLOOKUP has no qualifying starting date.
This formula returns text for an invalid or uncovered date. If later calculations require a numeric rate, you might prefer a blank or a numeric error indicator instead; choose an output that downstream formulas can handle deliberately. Do not remove the end-date test simply to make VLOOKUP return a value for every date: doing so can incorrectly extend the last period indefinitely.
How approximate matching finds the period
With TRUE as VLOOKUP’s final argument, Excel looks for an exact match or, if there is no exact match, the largest first-column value that is less than the lookup value. In a date-range table, that means a lookup date of February 14 selects the row starting February 1, provided the start-date column is sorted ascending. The end-date comparison then confirms that February 14 actually falls inside that selected period.
The method works well for consecutive, non-overlapping ranges. It can also work when there are gaps, because the end-date check rejects a date that falls after the selected row’s end. It does not reliably choose among overlapping ranges: approximate VLOOKUP selects by the start date, not by a rule such as “choose the narrowest range” or “use the newest matching record.” Decide how overlaps should be resolved before using the result for reporting or calculations.
For inclusive periods, the formula’s “less than or equal to” test treats both the start and end dates as part of the range. If your business rule treats the end as exclusive—for example, one period ends exactly when the next begins—adjust the comparison accordingly and document the convention. The important thing is to use one consistent rule so a boundary date is not counted twice or left out.
Use XLOOKUP when both boundaries must be tested
If you have Microsoft 365 or Excel 2021 or later, XLOOKUP can test the start and end columns directly. With start dates in A2:A4, end dates in B2:B4, rates in C2:C4, and the lookup date in E2, use:
=XLOOKUP(1,(E2>=$A$2:$A$4)*(E2<=$B$2:$B$4),$C$2:$C$4,"No matching period")
For February 14, 2026, this returns 6% from the example table. Each comparison produces TRUE for a qualifying row and FALSE for a row that fails that boundary test. Multiplying the two tests makes a row qualify only when both are TRUE; XLOOKUP searches for 1 and returns the corresponding rate. The ranges in the formula must be the same size.
This approach does not depend on the start-date column being sorted, because it tests every listed range. It is therefore convenient for unsorted data or when the table’s arrangement cannot be changed. It still needs a clear policy for overlaps: if more than one row meets both conditions, XLOOKUP returns the first matching row in the lookup arrays. XLOOKUP is not available in Excel 2019 and earlier, so use the VLOOKUP pattern or another compatible method in those versions.
Common mistakes and troubleshooting
- Using FALSE for the VLOOKUP match argument: FALSE asks for an exact match to the lookup date in the first column. It will not find a date that falls between two listed start dates. Use TRUE for the approximate-match method described above.
- Leaving start dates unsorted: Approximate VLOOKUP relies on ascending order in the first column. Sort the entire table together, not just column A, so each end date and rate stays on its correct row.
- Dates stored as text: A text value may look like a date but compare differently from a real Excel date. Check a suspicious cell’s value and convert the source data to real dates before relying on range comparisons.
- Incorrect column index: VLOOKUP counts columns from the left edge of its table range. In A2:C4, 2 means column B and 3 means column C. If you change the table range, check those indexes again.
- Overlapping or missing periods: Look for ranges whose boundaries conflict or leave gaps. A formula cannot infer which overlapping rate is intended, and a gap should usually produce an explicit no-match result.
- Unexpected time portions: Excel date-times can include a time value even when the cell is formatted to show only the date. A value at midnight and one at noon on the same calendar day are not identical. If times matter, define whether the range includes a whole end date or ends at a precise time.
For a quick visual check, inspect the source dates and the selected row together, particularly when a result appears off by one day. Regional date formats can also make ambiguous text such as 03/04/2026 mean different dates in different settings; unambiguous source dates reduce that risk.
Choosing between VLOOKUP and XLOOKUP
Use VLOOKUP with approximate match when the table is organized by ascending start date, periods do not overlap, and compatibility with older Excel versions matters. Its main advantage is that it is available in older releases and fits naturally with a sorted threshold table. Its main drawback is that it relies on that sorting and requires a separate check against the end date.
Choose XLOOKUP when you want the formula to express the two-boundary condition directly, or when the start-date list is not sorted. It is easier to adapt if the return column changes because you specify the return range rather than a numeric column index. However, it requires Excel 2021 or Microsoft 365, and it still returns only the first match if ranges overlap.
If you need to add together values for all records whose dates fall inside a period, rather than return a single matching row, a lookup may be the wrong tool. Functions such as SUMIFS are designed to aggregate values using multiple criteria. For a single applicable rate or category, use a lookup; for a total across many dated records, use an aggregation formula that matches the question.
Frequently Asked Questions
Can VLOOKUP check both a start date and an end date?
Not with a single built-in “between” match option. Use approximate VLOOKUP on a sorted start-date column and verify the end date, or use XLOOKUP with separate start and end comparisons.
Does VLOOKUP TRUE mean an exact match?
TRUE allows approximate matching. Excel can return the largest first-column value less than or equal to the lookup value, so the first column must be sorted ascending for this use. FALSE requests an exact match.
What if the date is before the first start date?
There is no earlier starting row for approximate VLOOKUP to select, so it returns an error. The example formula catches that case with IFERROR and displays “No matching period.”
Can I use VLOOKUP if date ranges overlap?
Only if you have a deliberate rule that makes the selected row acceptable. Approximate VLOOKUP chooses by start date, while XLOOKUP returns the first row that meets both comparisons. Neither automatically decides which overlapping period should take priority.
Why does my date look right but return the wrong result?
Check whether it is stored as a real date, whether the start-date column is sorted, and whether the table rows stayed aligned after sorting. Also check for hidden time values, overlapping periods, and an incorrect VLOOKUP column index.
Related
- Excel Custom Number Format: Show Numbers in Thousands
- Excel Shortcut to Select the Rest of a Column Down
- Excel Shortcut to Select an Entire Column
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Copy Values in Excel: Keep Results, Not FormulasTo copy only the displayed results of cells in Excel, copy the source range, select the destination’s top-left…
- Excel Formula for Last 4 Digits: RIGHT and FixesTo return the last four characters from a value in cell A2, use =RIGHT(A2,4). For example, if A2…
- Freeze a Formula in Excel: Keep Cell References FixedTo freeze a cell reference while copying a formula in Excel, edit the formula, select the reference, and…
- Excel MIN IF: Find the Smallest Value That Meets a TestTo find the smallest value in Excel that meets a condition, use MINIFS in Microsoft 365: with regions…
- COUNTIF Negative Numbers in Excel: Formula and TipsIf your values are in A2:A20, count the negative numbers with =COUNTIF(A2:A20,"<0"). COUNTIF returns the number of cells…
- Calculating Due Dates in Excel With Simple FormulasTo calculate a due date in Excel, add the number of calendar days to a valid start date:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.