Get the Latest Record for Each Customer in Excel (Last Price, Last Order, Last Status)

Get the Latest Record for Each Customer in Excel (Last Price, Last Order, Last Status) featured illustration

📎 This article includes 1 downloadable practice file ↓

⏱ 2 min read

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
  1. Latest date for a customer
  2. Value from that latest row
  3. Simpler when data is in date order: search from the bottom
  4. One row per customer: the latest of each (Excel 365)
  5. Where people go wrong
  6. Practice
  7. Continue learning

Latest date for a customer

=MAXIFS(A2:A201, D2:D201, J2)      ' J2 = customer name

MAXIFS (Excel 2019+) returns the biggest date matching the condition — the latest order. Format the cell as a date.

Advertisement

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

Advertisement

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))))
  1. Sort all rows newest first.
  2. For each row, XMATCH finds the first position of that customer.
  3. 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.

Advertisement

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

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong