# How to return the amount of the most recent order for a customer in Excel

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)`.

**Excel:** `=XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1)),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 |
| --- | --- | --- | --- | --- | --- |
| Customer | Order date | Amount |  | Customer | Fabrikam |
| Contoso | 2026-01-12 | 420 |  | Latest amount |  |
| Fabrikam | 2026-02-03 | 1250 |  |  |  |
| Contoso | 2026-03-18 | 310 |  |  |  |
| Northwind | 2026-01-29 | 880 |  |  |  |
| Fabrikam | 2026-04-07 | 1460 |  |  |  |
| Contoso | 2026-02-25 | 95 |  |  |  |
| Northwind | 2026-05-02 | 730 |  |  |  |
| Fabrikam | 2026-03-11 | 515 |  |  |  |

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

Result: $1,460

## 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.

## Other ways

- `=INDEX(C2:C9,MATCH(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1)),0))`: INDEX and MATCH, for Excel 2019 and earlier.
- `=TAKE(SORTBY(FILTER(C2:C9,A2:A9=F1),FILTER(B2:B9,A2:A9=F1),-1),1)`: 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.

## 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")`.

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/value-from-the-most-recent-date