'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Return the Latest Date Using MAXIFS or INDEX MATCH
Question details
The user needs to retrieve the most recent effective date for a specific individual when the source data contains multiple entries for the same person.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Searching for a person's most recent record in a dataset containing duplicate names or IDs.
- Observed behavior
- Standard INDEX and MATCH functions only return the first matching date they encounter, rather than the latest one.
Ensure that the dates in your source column are formatted as actual date values, not text strings. Also, verify that the lookup values like names or ID numbers match exactly without trailing spaces.
Use the MAXIFS Function to Find the Latest Date
MAXIFS is the most direct way to find the maximum value (latest date) based on one or multiple criteria without needing complex array formulas.
Click on the cell where you want the latest date to appear.
Type the formula =MAXIFS($D$2:$D$7, $A$2:$A$7, F2, $B$2:$B$7, G2), adjusting the ranges for your specific Date column and Criteria columns.
Press Enter. If the result appears as a standard number, format the cell as a Date using the Number Format dropdown in the Home tab.
Combine MAX and FILTER Functions
If you prefer dynamic arrays or need more complex filtering logic, nesting the FILTER function inside the MAX function works perfectly.
Use Advanced Formulas Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports modern functions like MAXIFS, FILTER, and dynamic arrays to help you manipulate complex data with ease.
- 1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the duplicate records.
- 2. Use the formula bar: Select a cell and type =MAXIFS( or =FILTER( to access the built-in syntax guides.
- 3. Select ranges: Easily click and drag across your criteria and date ranges to populate the formula automatically.
Frequently Asked Questions
Why does my MAXIFS formula return a zero?
A result of 0 usually means that there are no matches for your criteria, or the dates in your source range are stored as text rather than valid spreadsheet dates.
Can I use VLOOKUP or INDEX MATCH to find the latest date?
Standard VLOOKUP or INDEX MATCH formulas stop at the first match they find. To get the latest date using those functions, you must sort the source data in descending order by date first. Otherwise, it is better to use MAXIFS.
Should I match by name or ID number?
It is always better to use a unique identification number. Names can have inconsistent capitalization, typos, or duplicates (e.g., two people named John Smith), which can cause lookup errors.
