'%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 Maximum Matching Date Using MAXIFS in Excel
Question details
The user needs to retrieve the most recent (maximum) start date for a specific workflow step across multiple rows with the same ID, bypassing the limitation of INDEX/MATCH which only returns the first match.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Working with a dataset containing multiple process rows per ID and needing to extract the absolute latest date for a specific workflow stage.
- Observed behavior
- Using INDEX/MATCH only returns the first matching date found in the dataset, whereas the goal state is to return the absolute latest date matching multiple specific criteria.
Ensure your date column contains valid date values rather than text strings, and verify that your spreadsheet software version supports the MAXIFS function.
Use the MAXIFS Function to Find the Latest Date
The MAXIFS function allows you to return the maximum value from a range based on one or more criteria, making it ideal for finding the latest date for a specific ID and workflow step.
MAXIFS evaluates multiple conditions (like matching an ID and a specific workflow step) and returns the highest numeric value from the specified range. Because spreadsheet software stores dates as sequential serial numbers, the highest number logically corresponds to the most recent date.
Click on the cell where you want the latest matching date to appear.
Type the formula: =MAXIFS([STAGE_START_DATE], [ID], [@ID], [WORKFLOW_STEP], "Step 1"). If you are not using an Excel Table format, adjust this to use standard cell ranges (e.g., =MAXIFS(C2:C100, A2:A100, A2, B2:B100, "Step 1")).
Press Enter to execute the function. The cell may initially display a 5-digit number.
Right-click the result cell, select 'Format Cells', navigate to the Number tab, select 'Date', and choose your preferred date format. Click OK.
Use an Array Formula (MAX and IF) for Older Excel Versions
If you are using an older version of Excel that does not support MAXIFS, you can combine the MAX and IF functions in an array formula to achieve the exact same result.
Analyze Data and Use Advanced Functions Easily in WPS Spreadsheet
WPS Spreadsheet fully supports the MAXIFS function along with hundreds of other advanced formulas. It provides a highly compatible and smooth data analysis experience for processing complex datasets without lag.
- 1. Download WPS Office: Download and install WPS Office for free from the official website.
- 2. Open Your Dataset: Launch WPS Spreadsheet and open your existing data file.
- 3. Apply MAXIFS: Select the target cell and use the exact same MAXIFS formula as you would in Excel.
- 4. Format to Date: Use the Format Cells menu to apply the proper Date formatting to your formula results.
Frequently Asked Questions
Why does my MAXIFS result look like a random 5-digit number?
Spreadsheets store dates as sequential serial numbers (for example, 44562 represents a specific date in 2022). If you see a number like this, your formula is working correctly. You simply need to right-click the cell, select 'Format Cells', and apply a Date format.
Why is my MAXIFS formula returning a 0?
A result of 0 usually means no rows in your dataset meet all the specified criteria. Check for typos in your formula criteria, ensure your ranges are exactly the same size, and verify there are no hidden spaces in your data.
Can I use MAXIFS with more than two criteria?
Yes, the MAXIFS function can handle up to 126 pairs of criteria ranges and criteria. You just need to append the additional range and condition to the end of your formula.
Why doesn't INDEX/MATCH work for finding the latest date?
By default, INDEX/MATCH searches from top to bottom and stops at the very first match it finds. If there are multiple entries for the same ID, it will not check the rest to find a later date. MAXIFS is specifically designed to evaluate all matches and return the highest value.
