How to get the latest record for each customer in Excel
Excel formula
=FILTER(A2:C12,B2:B12=MAXIFS(B2:B12,A2:A12,A2:A12))Keep the rows whose date is the newest for that customer: =FILTER(A2:C12,B2:B12=MAXIFS(B2:B12,A2:A12,A2:A12)).
- Result on the example
- a 4-row list that spills from E2 in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
MAXIFS(B2:B12,A2:A12,A2:A12) works out, for every row, the latest date of that row's customer. Comparing it with the row's own date is TRUE only on each customer's newest order, so FILTER returns one row per customer. The list doesn't need to be sorted.
The Google Sheets version asks the same question the other way round: keep a row when no order for that customer is later.
=FILTER(A2:C12,B2:B12=MAXIFS(B2:B12,A2:A12,A2:A12))Result{4 rows × 3 cols} "Contoso", 46179, 1475, "Northwind", …
A2:C1211 rows × 3 cols: "Contoso", 5/2/2026, $1,200, "Fabrikam", …A range: 11 rows × 3 columns.
B2:B1211 rows × 1 col: 5/2/2026, 5/9/2026, 5/16/2026, 5/23/2026, …A range: 11 cells in one column.
A2:A1211 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", …A range: 11 cells in one column.
MAXIFS(B2:B12,A2:A12,A2:A12){11 rows × 1 col} 46179, 46193, 46179, 46186, …MAXIFS: Largest value that meets every condition.- max_range
B2:B1211 rows × 1 col: 5/2/2026, 5/9/2026, 5/16/2026, 5/23/2026, … - criteria_range1
A2:A1211 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", … - criteria1
A2:A1211 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", …
- max_range
B2:B12=MAXIFS(B2:B12,A2:A12,A2:A12){11 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
FILTER(A2:C12,B2:B12=MAXIFS(B2:B12,A2:A12,A2:A12)){4 rows × 3 cols} "Contoso", 46179, 1475, "Northwind", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:C1211 rows × 3 cols: "Contoso", 5/2/2026, $1,200, "Fabrikam", … - condition1
B2:B12=MAXIFS(B2:B12,A2:A12,A2:A12){11 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …
- range
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(FILTER(A2:C12,COUNTIFS(A2:A12,A2:A12,B2:B12,">"&B2:B12)=0)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:C12,COUNTIFS(A2:A12,A2:A12,B2:B12,">"&B2:B12)=0) | spills 4 × 3 | Counts later orders for the same customer and keeps rows with none. Same result. |
| =SORT(FILTER(A2:C12,B2:B12=MAXIFS(B2:B12,A2:A12,A2:A12)),1) | spills 4 × 3Different result | Sort the result by customer name. Contoso, Fabrikam, Litware, Northwind. |
Common mistakes
- Two orders for one customer on the same latest date both come back. Add a tie-breaker, such as the higher order number, if you need exactly one.
- Dates stored as text are ignored by MAXIFS, which then returns 0 and drops that customer.
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.
Get the latest record for each customer
E2, return the most recent order for each customer: one row per customer, with all three columns.Questions
How do I get the first order for each customer instead?
Use MINIFS: =FILTER(A2:C12,B2:B12=MINIFS(B2:B12,A2:A12,A2:A12)).
How do I return just the latest date per customer?
Use UNIQUE and MAXIFS: =HSTACK(UNIQUE(A2:A12),MAXIFS(B2:B12,A2:A12,UNIQUE(A2:A12))).
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.