# How to look up the price in effect on a date in Excel

Find the latest effective date on or before the order date with MAXIFS, then look up the price for that product and date: `=XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1)),C2:C9)`.

**Excel:** `=XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1)),C2:C9)` (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 | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Product | Effective from | Price |  | Product | Espresso beans 1 kg | Order date | 2026-02-10 |
| Espresso beans 1 kg | 2025-01-01 | 24.5 |  | Price |  |  |  |
| Green tea 100 bags | 2025-01-01 | 8.75 |  |  |  |  |  |
| Espresso beans 1 kg | 2025-07-01 | 26 |  |  |  |  |  |
| Oat milk 6-pack | 2025-03-15 | 17.4 |  |  |  |  |  |
| Green tea 100 bags | 2025-09-01 | 9.25 |  |  |  |  |  |
| Espresso beans 1 kg | 2026-01-15 | 27.9 |  |  |  |  |  |
| Oat milk 6-pack | 2026-01-01 | 18.2 |  |  |  |  |  |
| Espresso beans 1 kg | 2026-04-01 | 29.5 |  |  |  |  |  |

Formula in F2: `=XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1)),C2:C9)`

Result: $27.90

## How it works

The price in effect is the one with the latest effective date that isn't after the order date. MAXIFS finds that date for this product: of January 1 2025, July 1 2025 and January 15 2026, the latest on or before February 10 2026 is January 15. XLOOKUP then returns the price on the row with this product and that date, $27.90.

Matching both the product and the date matters because other products changed price on the same days.

## Other ways

- `=XLOOKUP(1,(A2:A9=F1)*(B2:B9<=H1),C2:C9,,0,-1)`: Shorter, when the list is kept in date order: search from the bottom for the last row of the product dated on or before the order.
- `=INDEX(C2:C9,MATCH(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1)),0))`: For Excel 2019 (Ctrl+Shift+Enter).

## Common mistakes

- An order dated before the product's first price returns #N/A, because MAXIFS returns 0. Wrap the formula in IFNA with a message.
- Two rows with the same product and effective date return the first. Keep one price per product per date.

## Questions

**How do I get today's price?**

Use `TODAY()` in place of H1.

**How do I price a whole order list?**

Use the same formula on each line with relative references to the line's product and date, and lock the price list with $.

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/price-in-effect-on-a-date