Where “date” is the named range D5:D14. Note: this is an array formula and must be entered with Control + Shift + Enter. The logical test generates an array of TRUE / FALSE values, where TRUE corresponds to dates greater than or equal to today: When a result is TRUE, the date is passed into array returned by IF. When a result is FALSE, the date is replaced by the boolean FALSE. The IF function returns the following array to MIN: The MIN function then ignores the FALSE values, and returns the smallest date value (43371), which is the date Sept. 28, 2018 in Excel’s date system.
Getting the movie name
To display the movie associated with the “next date”", we use INDEX and MATCH: Inside INDEX, MATCH finds the position of the date in G6 in the list of dates. This position, 4 in the example, is returned to INDEX as a row number: and INDEX returns the movie at that position, “The Dark Knight”.
All in one formula
To return the next Movie in a single formula, you can use this array formula:
With MINIFS
If you have a newer version of Excel, you can use the MINIFS function instead of the array formula in G6: MINIFS was introduced in Excel 2016 via Office 365.
Handling errors
The formula on this page will work even when events aren’t sorted by date. However, if there are no upcoming dates, the MIN function will return zero instead of an error. This will display as the date “0-Jan-00” in G6, and the INDEX and MATCH formula will throw an #N/A error, since there is no zero-th row to get a value from. To trap this error, you can replace MIN with the SMALL function, then wrap the whole formula in IFERROR like this: Unlike MIN, the SMALL function will throw an error when a value isn’t found, so IFERROR can be used to manage the error.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
