How to return the amount of the most recent order for a customer in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1)),C2:C9)Find the customer's latest date with MAXIFS, then look up the row with both: =XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1)),C2:C9).
- Result on the example
- $1,460 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
MAXIFS finds the customer's latest order date. Multiplying the two tests gives 1 only on the row with that customer and that date, and XLOOKUP returns the amount from it. Testing the customer as well matters when two customers ordered on the same day.
=XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1)),C2:C9)Result1460
A2:A98 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", …A range: 8 cells in one column.
F1"Fabrikam"The value in F1.
A2:A9=F1{8 rows × 1 col} FALSE, TRUE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
B2:B98 rows × 1 col: 1/12/2026, 2/3/2026, 3/18/2026, 1/29/2026, …A range: 8 cells in one column.
MAXIFS(B2:B9,A2:A9,F1)46119MAXIFS: Largest value that meets every condition.- max_range
B2:B98 rows × 1 col: 1/12/2026, 2/3/2026, 3/18/2026, 1/29/2026, … - criteria_range1
A2:A98 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", … - criteria1
F1"Fabrikam"
- max_range
B2:B9=MAXIFS(B2:B9,A2:A9,F1){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){8 rows × 1 col} 0, 0, 0, 0, …The * operator multiplies.
C2:C98 rows × 1 col: $420, $1,250, $310, $880, …A range: 8 cells in one column.
XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1)),C2:C9)1460XLOOKUP: 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){8 rows × 1 col} 0, 0, 0, 0, … - return_array
C2:C98 rows × 1 col: $420, $1,250, $310, $880, …
- 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)),C2:C9). We ran it in Google Sheets mode on the same data and it returns $1,460.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(C2:C9,MATCH(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1)),0)) | $1,460 | INDEX and MATCH, for Excel 2019 and earlier. |
| =TAKE(SORTBY(FILTER(C2:C9,A2:A9=F1),FILTER(B2:B9,A2:A9=F1),-1),1)Excel only | $1,460 | Sorts the customer's orders newest first and takes the top one. Excel 365. |
Common mistakes
XLOOKUP(MAXIFS(…),B2:B9,C2:C9)without the customer test can return another customer's order from the same date.- With two orders on the latest date, XLOOKUP returns the first. Use SUMIFS on the customer and the date to add them instead.
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.
Return the amount of the most recent order for a customer
F2, return the amount of the most recent order for the customer in F1.Questions
How do I return the whole latest row?
Point XLOOKUP at all the columns: =XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1)),A2:C9).
What if the customer has no orders?
XLOOKUP returns #N/A. Add a fourth argument: =XLOOKUP(1,…,C2:C9,"No orders").
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.