# How to look up a value between a start and end date in Excel

Look for the row where the date is on or after the start and on or before the end: `=XLOOKUP(1,(B2:B7=G1),A2:A7,"No promotion")`.

**Excel:** `=XLOOKUP(1,(B2:B7<=G1)*(C2:C7>=G1),A2:A7,"No promotion")` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Promotion | Start | End | Discount |  | Order date | 2026-05-22 |
| Spring sale | 2026-03-20 | 2026-04-05 | 0.15 |  | Promotion |  |
| Mother's Day | 2026-05-01 | 2026-05-10 | 0.2 |  |  |  |
| Memorial Day | 2026-05-21 | 2026-05-25 | 0.25 |  |  |  |
| Summer kickoff | 2026-06-15 | 2026-06-30 | 0.1 |  |  |  |
| Back to school | 2026-08-01 | 2026-08-20 | 0.15 |  |  |  |
| Labor Day | 2026-09-04 | 2026-09-07 | 0.2 |  |  |  |

Formula in G2: `=XLOOKUP(1,(B2:B7<=G1)*(C2:C7>=G1),A2:A7,"No promotion")`

Result: Memorial Day

## How it works

Each test gives TRUE or FALSE per row: `B2:B7=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.

## Other ways

- `=INDEX(A2:A7,MATCH(1,(B2:B7<=G1)*(C2:C7>=G1),0))`: 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")`: 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>G1` or strip the times.

## Questions

**How do I return the discount instead?**

Change the return range: `=XLOOKUP(1,(B2:B7=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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/lookup-between-start-and-end-dates