How to Find the Next Larger or Smaller Date Using Excel Formulas
Question details
The user wants to find the row position of the next date that is strictly greater than a minimum date, or strictly smaller than a maximum date, within an ascending date range.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing date-based data where the exact target date might not exist, requiring the relative row number of the closest subsequent or preceding date from a list.
- Observed behavior
- Needs to output the relative row position of the nearest matching larger or smaller date from a specified range.
Ensure your dataset is properly formatted as Dates rather than text, and sort your date range in ascending order to guarantee that relative row positions match the logical flow of time.
Use MATCH combined with MINIFS and MAXIFS
This method combines the MATCH function with MINIFS (to find the next larger date) and MAXIFS (to find the next smaller date) to precisely locate the correct row position.
The MINIFS and MAXIFS functions are excellent for applying logical criteria (like greater than or less than) to ranges. By embedding them inside a MATCH function, you can retrieve the exact row position instead of just the date value itself.
Click on the blank cell where you want the row position of the next greater date to be displayed.
Type the formula =MATCH(MINIFS(E4:E18,E4:E18,">"&E2),E4:E18) into the formula bar. In this example, E4:E18 is your ascending date range, and E2 contains your minimum target date.
To find the row position of the next smaller date, select a different cell and type =MATCH(MAXIFS(E4:E18,E4:E18,"<"&F2),E4:E18), where F2 is the maximum target date.
Press the Enter key. The cells will now evaluate the criteria and display the relative row positions of the qualifying dates within the selected range.
Use the XMATCH Function
For modern spreadsheet users, XMATCH provides a more streamlined, single-function approach to finding exact matches or the next smaller/larger items.
Easily Manage Date Formulas with WPS Office
WPS Spreadsheet fully supports advanced array functions like XMATCH, MINIFS, and MAXIFS, helping you locate specific dates within large datasets effortlessly. It provides an intuitive interface, syntax guidance, and seamless compatibility with Microsoft Excel files.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open the document containing your date ranges.
- 2. Start your formula: Click on a blank cell and type the equals sign (=) followed by MATCH or XMATCH.
- 3. Follow the tooltip guidance: Use the on-screen formula tooltip to quickly select your date ranges, define match modes, and input logical criteria.
- 4. Get instant results: Press Enter to instantly find your target date's relative position with lightning-fast calculation speeds.
Frequently Asked Questions
Why is my MINIFS or MAXIFS formula returning a #N/A or 0 error?
This usually happens if no dates in the specified range meet your criteria (e.g., there are no dates strictly greater than your target). It can also happen if your logical operators like ">" are not enclosed in quotes or properly concatenated using the ampersand (&) with the cell reference.
Does the date list have to be sorted for XMATCH to work correctly?
While XMATCH can operate on unsorted lists when just finding exact matches, using match modes 1 (next larger) or -1 (next smaller) performs best and yields logically sound results when your date list is sorted in ascending order.
How can I return the actual date value instead of its row position?
To get the actual date instead of the position, you can omit the MATCH function. Simply use =MINIFS(E4:E18,E4:E18,">"&E2) to get the actual next larger date. Alternatively, if using XMATCH, wrap it inside the INDEX function like this: =INDEX(E4:E18, XMATCH(E2,E4:E18,1)).
