How to get the most recent entry for every customer in Excel
Excel formula
=SORT(FILTER(A2:C11,B2:B11=MAXIFS(B2:B11,A2:A11,A2:A11)))Keep the rows whose date is that customer's latest: =SORT(FILTER(A2:C11,B2:B11=MAXIFS(B2:B11,A2:A11,A2:A11))).
- 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:B11,A2:A11,A2:A11) works out, for every row, the latest date of that row's customer. FILTER keeps the rows whose own date equals it, one per customer, and SORT puts them in customer order.
The Google Sheets version keeps the rows for which COUNTIFS finds no later order from the same customer, which is the same set of rows.
=SORT(FILTER(A2:C11,B2:B11=MAXIFS(B2:B11,A2:A11,A2:A11)))Result{4 rows × 3 cols} "Contoso", 46128, 760, "Fabrikam", …
A2:C1110 rows × 3 cols: "Contoso", 4/2/2026, $420, "Fabrikam", …A range: 10 rows × 3 columns.
B2:B1110 rows × 1 col: 4/2/2026, 4/5/2026, 4/16/2026, 4/12/2026, …A range: 10 cells in one column.
A2:A1110 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", …A range: 10 cells in one column.
MAXIFS(B2:B11,A2:A11,A2:A11){10 rows × 1 col} 46128, 46131, 46128, 46138, …MAXIFS: Largest value that meets every condition.- max_range
B2:B1110 rows × 1 col: 4/2/2026, 4/5/2026, 4/16/2026, 4/12/2026, … - criteria_range1
A2:A1110 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", … - criteria1
A2:A1110 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", …
- max_range
B2:B11=MAXIFS(B2:B11,A2:A11,A2:A11){10 rows × 1 col} FALSE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
FILTER(A2:C11,B2:B11=MAXIFS(B2:B11,A2:A11,A2:A11)){4 rows × 3 cols} "Contoso", 46128, 760, "Fabrikam", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:C1110 rows × 3 cols: "Contoso", 4/2/2026, $420, "Fabrikam", … - condition1
B2:B11=MAXIFS(B2:B11,A2:A11,A2:A11){10 rows × 1 col} FALSE, FALSE, TRUE, FALSE, …
- range
SORT(FILTER(A2:C11,B2:B11=MAXIFS(B2:B11,A2:A11,A2:A11))){4 rows × 3 cols} "Contoso", 46128, 760, "Fabrikam", …SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.- range
FILTER(A2:C11,B2:B11=MAXIFS(B2:B11,A2:A11,A2:A11)){4 rows × 3 cols} "Contoso", 46128, 760, "Fabrikam", …
- range
In Google Sheets
Google Sheets needs a different formula: =SORT(FILTER(A2:C11,COUNTIFS(A2:A11,A2:A11,B2:B11,">"&B2:B11)=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:C11,B2:B11=MAXIFS(B2:B11,A2:A11,A2:A11)) | spills 4 × 3Different result | Without SORT the rows stay in their original order. Original order: Contoso, Fabrikam, Northwind, Litware. |
Common mistakes
- If a customer has two orders on the latest date, both rows are kept. Add a time or an order number to break ties.
- MAXIFS with a range as its criteria needs Excel 2021 or Microsoft 365.
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 most recent entry for every customer
E2, return one row per customer: the customer's most recent order with its date and amount, sorted by customer.Questions
How do I get the first order per customer instead?
Use MINIFS: =SORT(FILTER(A2:C11,B2:B11=MINIFS(B2:B11,A2:A11,A2:A11))).
How do I do it for one customer only?
Use XLOOKUP with MAXIFS; see the latest status for an ID.
Is there a shortcut in Google Sheets?
Sort newest first and keep the first row per customer with SORTN: =SORTN(SORT(A2:C11,2,FALSE),9^9,2,1,TRUE).
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.