Return Each Customer’s Latest Product Purchase in Excel
Question details
Create a unique customer list and return the product associated with each customer’s most recent purchase date.

- 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.
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.
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.
In cell H3, enter =UNIQUE(B2:B21). Excel spills the unique customer names into the cells below.
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.
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.

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. Open the purchase table: Open your Excel file in WPS Spreadsheet and copy the complete purchase table, including headers, to a new sheet.
- 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. 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. 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.

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.




