📎 This article includes 1 downloadable practice file ↓
Transaction lists repeat customers many times. Reports usually want only the latest: last order date, last price paid, current status, newest address. Here’s how to pick the most recent row for each key.
In this article
Latest date for a customer
=MAXIFS(A2:A201, D2:D201, J2) ' J2 = customer nameMAXIFS (Excel 2019+) returns the biggest date matching the condition — the latest order. Format the cell as a date.
Value from that latest row
=XLOOKUP(1, (D2:D201=J2)*(A2:A201=MAXIFS(A2:A201, D2:D201, J2)), I2:I201)Find the row where the customer matches and the date is their latest; return the amount. If two orders share the latest date, it returns the first of them.
Simpler when data is in date order: search from the bottom
=XLOOKUP(J2, D2:D201, I2:I201, "None", 0, -1)The last argument -1 makes XLOOKUP search from the end, so it returns the last occurrence — the newest row, provided the sheet is sorted oldest to newest. In older Excel the classic is =LOOKUP(2, 1/(D2:D201=J2), I2:I201) (explained in the last-match trick).
One row per customer: the latest of each (Excel 365)
=LET(d, SORTBY(A2:I201, A2:A201, -1), c, CHOOSECOLS(d, 4),
FILTER(d, XMATCH(c, c) = SEQUENCE(ROWS(c))))- Sort all rows newest first.
- For each row, XMATCH finds the first position of that customer.
- Keep rows where the first position equals the row’s own position — i.e. the first (newest) row of each customer.
A shorter alternative many people use: sort newest first and then Data › Remove Duplicates on the customer column — Excel keeps the first, newest row.
Where people go wrong
- Dates stored as text — MAXIFS returns 0. Check with
ISNUMBER(A2). - Search-from-last on unsorted data returns the last row, not the latest date. Use the MAXIFS version when order isn’t guaranteed.
- Same-day duplicates — add a time or order number as a tie-breaker.
- Customer spelt two ways — the latest record splits across both spellings; standardise names first.
Practice
Download the combo practice workbook below: a Sales sheet of 200 orders plus tasks for the formulas on this page, each with an automatic ✓ check and an Answers sheet.
More: all formula combos · Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.