Function Problems

Return Each Customer’s Latest Product Purchase in Excel

Khadija KhanKhadija Khan Nov 18, 2026 868 views

Question details

Create a unique customer list and return the product associated with each customer’s most recent purchase date.

Return Each Customer’s Latest Product Purchase in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A purchase table contains dates, customer names, and product IDs or names, and the user wants one latest product for each customer.
Observed behavior
The desired result is a list of customers paired with products from their latest purchase dates.
Before you start

Confirm that purchase dates in column A are recognized as dates, customer names are in column B, and products are in column D. Leave space for the customer list to spill and adjust the formula ranges if your data is elsewhere.

Solution 1Recommended

Use UNIQUE and FILTER to find each customer’s latest product

Use this method in Excel for Microsoft 365 to create a customer list and return one product per customer.

1
Create the customer list

In cell H3, enter =UNIQUE(B2:B21). Excel spills the unique customer names into the cells below.

2
Return the latest product

In cell I3, enter =INDEX(FILTER($D$2:$D$21,($B$2:$B$21=H3)*($A$2:$A$21=MAX(FILTER($A$2:$A$21,$B$2:$B$21=H3)))),1). The formula finds the latest date for the customer in H3 and returns the first matching product.

3
Fill the formula down

Select I3 and drag its fill handle down alongside the customer list. Replace the column D range with the product-name range if you want names instead of IDs.

Use UNIQUE and FILTER to find each customer’s latest product
Purchases on the same date: If a customer has multiple products on the latest date, the formula returns the first matching product in the source range. To return all matches for one customer, remove INDEX(...,1) and leave room for the FILTER results to spill.
Complete this task in WPS

Find the Latest Product per Customer in WPS Spreadsheet

Sort a copy of the purchase table by date, then remove duplicate customers to keep one product from each customer’s latest purchase.

  1. 1. Open the purchase table: Open your Excel file in WPS Spreadsheet and copy the complete purchase table, including headers, to a new sheet.
  2. 2. Sort by purchase date: Select the copied table, open Data > Sort, and sort the purchase date column from newest to oldest. Keep all columns in the sort.
  3. 3. Remove duplicate customers: With the full table selected, open Data > Duplicates > Remove Duplicates. Select only the customer column as the duplicate criterion and confirm.
  4. 4. Review the results: Check the remaining customer, date, and product columns. The first row for each customer is retained; if purchases share the latest date, the result depends on their order after sorting.
Create a latest-product list without writing formulasOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet sorting and duplicate-removal tools
microsoft office alternative - wps office

Frequently Asked Questions

What if more than one product was purchased on the latest date?

The INDEX and FILTER formula returns the first matching product in the source range. To display all matching products for one customer, use the FILTER portion without INDEX and leave empty cells below it.

Why does the customer list show a #SPILL! error?

The UNIQUE formula needs empty cells below H3. Clear anything blocking its spill range, then check the formula again.

Can I return a product name instead of a product ID?

Yes. In the formula in I3, replace $D$2:$D$21 with the range containing product names. Keep the date, customer, and product ranges the same size.

Will the Excel formulas work in older versions of Excel?

UNIQUE and FILTER require a version that supports dynamic array functions, such as Excel for Microsoft 365. In an older version, you can instead sort the full table by date and remove duplicate customers, retaining the first row for each customer.