How to look up the price in effect on a date in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1)),C2:C9)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).
- Result on the example
- $27.90 in
F2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
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.
=XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1)),C2:C9)Result27.9
A2:A98 rows × 1 col: "Espresso beans 1 kg", "Green tea 100 bags", "Espresso beans 1 kg", "Oat milk 6-pack", …A range: 8 cells in one column.
F1"Espresso beans 1 kg"The value in F1.
A2:A9=F1{8 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
B2:B98 rows × 1 col: 1/1/2025, 1/1/2025, 7/1/2025, 3/15/2025, …A range: 8 cells in one column.
H12/10/2026The value in H1.
"<="&H1"<=46063"The & operator joins as text.
MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1)46037MAXIFS: Largest value that meets every condition.- max_range
B2:B98 rows × 1 col: 1/1/2025, 1/1/2025, 7/1/2025, 3/15/2025, … - criteria_range1
A2:A98 rows × 1 col: "Espresso beans 1 kg", "Green tea 100 bags", "Espresso beans 1 kg", "Oat milk 6-pack", … - criteria1
F1"Espresso beans 1 kg" - criteria_range2
B2:B98 rows × 1 col: 1/1/2025, 1/1/2025, 7/1/2025, 3/15/2025, … - criteria2
"<="&H1"<=46063"
- max_range
B2:B9=MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1){8 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1){8 rows × 1 col} 0, 0, 0, 0, …The * operator multiplies.
C2:C98 rows × 1 col: $24.50, $8.75, $26.00, $17.40, …A range: 8 cells in one column.
XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1)),C2:C9)27.9XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
1 - lookup_array
A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1){8 rows × 1 col} 0, 0, 0, 0, … - return_array
C2:C98 rows × 1 col: $24.50, $8.75, $26.00, $17.40, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1)),C2:C9). We ran it in Google Sheets mode on the same data and it returns $27.90.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =XLOOKUP(1,(A2:A9=F1)*(B2:B9<=H1),C2:C9,,0,-1) | $27.90 | 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)) | $27.90 | 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.
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 the price in effect on a date
F2, return the price of the product in F1 that was in effect on the order date in H1.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 $.
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.