How to look up a value between a start and end date in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(1,(B2:B7<=G1)*(C2:C7>=G1),A2:A7,"No promotion")Look for the row where the date is on or after the start and on or before the end: =XLOOKUP(1,(B2:B7<=G1)*(C2:C7>=G1),A2:A7,"No promotion").
- Result on the example
- Memorial Day in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
Each test gives TRUE or FALSE per row: B2:B7<=G1 is "started by then" and C2:C7>=G1 is "not ended yet". Multiplying them gives 1 only for a promotion that covers the date. XLOOKUP returns the name on that row, or the message when the date falls in a gap.
=XLOOKUP(1,(B2:B7<=G1)*(C2:C7>=G1),A2:A7,"No promotion")Result"Memorial Day"
B2:B76 rows × 1 col: 3/20/2026, 5/1/2026, 5/21/2026, 6/15/2026, …A range: 6 cells in one column.
G15/22/2026The value in G1.
B2:B7<=G1{6 rows × 1 col} TRUE, TRUE, TRUE, FALSE, …The <= operator tests at most.
C2:C76 rows × 1 col: 4/5/2026, 5/10/2026, 5/25/2026, 6/30/2026, …A range: 6 cells in one column.
C2:C7>=G1{6 rows × 1 col} FALSE, FALSE, TRUE, TRUE, …The >= operator tests at least.
B2:B7<=G1)*(C2:C7>=G1{6 rows × 1 col} 0, 0, 1, 0, …The * operator multiplies.
A2:A76 rows × 1 col: "Spring sale", "Mother's Day", "Memorial Day", "Summer kickoff", …A range: 6 cells in one column.
XLOOKUP(1,(B2:B7<=G1)*(C2:C7>=G1),A2:A7,"No promotion")"Memorial Day"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
1 - lookup_array
B2:B7<=G1)*(C2:C7>=G1{6 rows × 1 col} 0, 0, 1, 0, … - return_array
A2:A76 rows × 1 col: "Spring sale", "Mother's Day", "Memorial Day", "Summer kickoff", … - if_not_found
"No promotion"
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(1,(B2:B7<=G1)*(C2:C7>=G1),A2:A7,"No promotion"). We ran it in Google Sheets mode on the same data and it returns Memorial Day.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A7,MATCH(1,(B2:B7<=G1)*(C2:C7>=G1),0)) | Memorial Day | For Excel 2019 and earlier (Ctrl+Shift+Enter in 2016 and older). Returns #N/A in a gap. |
| =FILTER(A2:A7,(B2:B7<=G1)*(C2:C7>=G1),"No promotion") | Memorial Day | Lists every promotion that covers the date, if they can overlap. In Google Sheets, put IFERROR around it for the message. |
Common mistakes
- An approximate match on the start date alone can't see gaps: June 5 would return Memorial Day, which ended May 25.
- If the end dates include times, like 5/25/2026 11:59 PM, compare with
C2:C7+1>G1or strip the times.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Look up a value between a start and end date
G2, return the promotion that was running on the order date in G1, or "No promotion" if none was.Questions
How do I return the discount instead?
Change the return range: =XLOOKUP(1,(B2:B7<=G1)*(C2:C7>=G1),D2:D7,0), with 0 when no promotion applies.
How do I add a product condition?
Multiply a third test, such as *(E2:E7=G3) for a product column.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.