Function Problems

How to Find the Latest Date for Each ID in Excel

Phi Hung Vo Sep 28, 2026 871 views

Question details

The user needs to extract the most recent date for each unique ID in a dataset, and optionally retrieve related data such as names from the same row.

How to Find the Latest Date for Each ID in Excel

Product: Excel
Device & OS: not provided
Scenario: Organizing and analyzing datasets where multiple entries exist for the same ID, requiring the identification of the most recent transaction or event.
Observed behavior: The user wants a formula-based approach to return a consolidated list of unique IDs alongside their latest dates and corresponding row information.

Before you start

Ensure your date column is properly formatted as Dates and not stored as text. Verify that you are using a version of Excel or a spreadsheet program (like Microsoft 365 or WPS Office) that supports dynamic array functions if you plan to use UNIQUE and LET.

Solution 1Recommended

Use Dynamic Arrays (UNIQUE and MAXIFS) to Find Latest Dates

This is the most efficient method for Microsoft 365 or WPS Office users to automatically list unique IDs and calculate their maximum dates in one go.

By combining the LET, UNIQUE, and MAXIFS functions, you can create a dynamic array that automatically updates if new data is added to your list. The LET function is used to define variables, keeping the formula clean and easy to read.

Identify your data ranges

Assume your IDs are located in cells A2:A8 and your corresponding Dates are in cells B2:B8.

Enter the dynamic formula

Select an empty cell (for example, D2) and type the following formula: =LET(id,A2:A8,dt,B2:B8,uid,UNIQUE(id),HSTACK(uid,MAXIFS(dt,id,uid)))

Format the result as a date

The formula will spill the results into two columns. The second column will display serial numbers. Select these numbers, right-click, choose 'Format Cells', and apply a 'Date' format to see the actual latest dates.

Use Dynamic Arrays (UNIQUE and MAXIFS) to Find Latest Dates

Dynamic Spill: Because this is a dynamic array formula, you do not need to drag it down. It will automatically spill the results for all unique IDs found in your dataset.

Solution 2

Retrieve Associated Row Data with XLOOKUP

Once you have the latest date for an ID, use XLOOKUP with multiple criteria to extract corresponding details, like a person's name.

Solution 3

Find the Latest Date Using a Pivot Table

An alternative approach if you prefer not to use complex formulas or are using an older version of Excel that does not support dynamic arrays.

Advanced Spreadsheet Tool

Find Latest Dates and Extract Data Using WPS Spreadsheet

WPS Spreadsheet provides robust support for modern dynamic array formulas like UNIQUE, FILTER, MAXIFS, and XLOOKUP, making it effortless to analyze complex data sets and find the newest records per ID seamlessly.

1. Open your dataset: Launch WPS Spreadsheet and open your .xlsx file containing the IDs, dates, and related data.
2. Apply the unique formula: Use the UNIQUE function in an empty cell to extract all distinct IDs into a new column instantly.
3. Calculate the maximum date: Use the MAXIFS function next to your newly created unique IDs to return the latest date for each specific ID.
4. Format the results: Select your result column, press Ctrl+1 to open the Format Cells dialog, and select your preferred Date format.

Fully compatible with Microsoft Excel formulas and .xlsx formatSupports advanced dynamic arrays for automated data extractionLightweight, fast, and free to use for everyday spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is my formula returning a 5-digit number instead of a date?

Spreadsheet programs store dates as sequential serial numbers for calculation purposes (e.g., 45514 represents August 10, 2024). To fix this, simply select the cells containing the numbers, open the cell formatting menu, and apply a 'Date' format.

Why does the formula show a #NAME? error?

The #NAME? error usually occurs if you are using an older version of Excel that does not support newer functions like LET, UNIQUE, or XLOOKUP. In this case, you should use the Pivot Table method or standard array formulas using MAX and IF.

Can I get the entire row corresponding to the latest date without helper columns?

Yes, if you use an advanced dynamic array approach, you can nest functions. However, the easiest way to return the whole row is to find the max date per ID first using MAXIFS, and then wrap a FILTER function around your entire dataset searching for rows that match both the ID and the calculated maximum date.

Excel & Data Problems