Combine the MIN and IF Functions in an Array Formula GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office

How to Combine the MIN and IF Functions in an Excel Array Formula

Find the smallest value for a range of data meeting a specific criterion

By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on February 19, 2021 Tweet Share Email Tweet Share Email MS Office Excel Word Powerpoint Outlook

What to Know

Convert MIN IF formula to an array: Press and hold Ctrl+Shift, then press Enter to create a formula in the formula bar.Because the IF function is nested inside the MIN function, the entire IF function becomes the sole argument for the MIN function.The arguments for the IF function are: logical_test (required), value_if_true (required), and value_if_false (optional). The best way to understand how to combine the MIN and IF functions in Excel is with an example. This tutorial example contains heat times for two events from a track meet—the 100 and 200-meter sprints, and applies to Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, and Excel 2010.

What Is a MIN IF Array

Using a MIN IF array formula allows you to find the fastest heat time for each race with one formula. The job of each part of the formula is as follows: The MIN function finds the fastest or smallest time for the event chosen. The IF function allows us to choose the race by setting a condition using the race names. The array formula lets the IF function test for multiple conditions in a single cell, and when the condition is met, the array formula determines what data (race times) the MIN function examines to find the fastest time.

MIN IF Nested Formula Syntax and Arguments

The syntax for the MIN IF formula is: Because the IF function is nested inside the MIN function, the entire IF function becomes the sole argument for the MIN function. The arguments for the IF function are: logical_test (required) — A value or expression that is tested to whether it is true or false.value_if_true (required) — The value that is displayed if logical_test is true.value_if_false (optional) — The value that is displayed if logical_test is false. In the example, the logical test tries to find a match for the race name typed into cell D10 of the worksheet. The value_if_true argument is, with the help of the MIN function, the fastest time for the chosen race. The value_if_false argument is omitted since it is not needed and its absence shortens the formula. If a race name that is not in the data table, such as the 400-meter race, is typed into cell D10, the formula returns a zero.

Excel' s MIN IF Array Formula Example

Enter the following tutorial data into cells D1 to E9: Race Times
Race Time (sec)
100 meters 11.77
100 meters 11.87
100 meters 11.83
200 meters 21.54
200 meters 21.50
200 meters 21.49
Race Fastest Heat (sec)
In cell D10, type "100 meters" (without the quotes). The formula will look in this cell to find which of the races you want it to find the fastest time for.

Entering the MIN IF Nested Formula

Since you are creating both a nested formula and an array formula, you need to type the entire formula into a single worksheet cell. After you enter the formula do not press the Enter key on the keyboard or click a different cell with the mouse; you need to turn the formula into an array formula. To do that, select cell E10, the location where the formula results will be displayed, and type: =MIN(IF(D3:D8=D10,E3:38))

Creating the Array Formula

Now that you've entered the MIN IF formula, you need to convert it to an array. Follow these steps to do so. Press and hold the Ctrl and Shift keys on the keyboard. Press the Enter key on the keyboard to create the array formula. The answer 11.77 appears in cell F10 because it is the fastest (smallest) time for the three 100 meter sprint heats. The complete array formula appears in the formula bar above the worksheet. Because the Ctrl, Shift, and Enter keys on the keyboard are pressed simultaneously after the formula has been typed in, the resulting formulas are sometimes referred to as CSE formulas.

Test the Formula

Test the formula by finding the fastest time for the 200 meters. Type 200 meters into cell D10 and press the Enter key on the keyboard. The formula should return the time of 21.49 seconds in cell E10. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire Find The MEDIAN IF Your Formula Meets Criteria in Excel How to Use the Google Spreadsheets AVERAGE Function How to Combine the ROUND and SUM Functions in Excel How to Nest Multiple IF Functions in Excel Ignore Error Values When Finding the Average in Excel How to Use the Round Function in Excel Excel MAX IF Array Formula Perform Multiple Calculations With Excel Array Formulas Use the Excel RIGHT Function to Extract Characters How to Count Data in Selected Cells With Excel's COUNTIF Function Excel SUMIFS: Sum Only Values Meeting Multiple Criteria How to Create an Excel Lookup Formula With Multiple Criteria How to Round Numbers Down in Excel With the ROUNDDOWN Function How to Use Excel's MROUND Function Use Excel's EOMONTH Function to Add or Subtract Months How to Use the Excel DATE Function Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies