Excel MAX IF Array Formula

The MAX IF array formula searches for the maximum value based on specific criteria. In the example below, we use MAX IF to find the best (highest) result for two track and field events – the high jump and pole vault – just by changing the search criterion.

CSE Formulas

You create array formulas by pressing the Ctrl, Shift, and Enter keys on the keyboard in succession once you've typed in the formula.

Because of the keys pressed to create the array formula, people sometimes call them CSE formulas.

MAX IF Nested Formula Syntax and Arguments

The job of each part of the formula is:

• The MAX function finds the highest result for the event chosen.
• The IF function allows us to choose the event by setting a condition using the event names.
• The array formula lets the IF function test for multiple conditions in a single cell, and, when the data meets a condition, the array formula determines what data (event results) the MAX function will examine to find the best result.

The syntax for the MAX IF formula is:

• Since the IF function nests inside the MAX function, the entire IF function becomes the sole argument for the MAX function.

The arguments for the IF function are:

• logical_test (required): A value or expression that is tested to see if 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 this example:

• The logical test tries to find a match for the event name typed into cell D10 of the worksheet.
• The value_if_true argument will be, with the help of the MAX function, the best result for the chosen event.
• The value_if_false argument isn't necessary in this case, and its absence will shorten the formula. If an event name that is not in the data table — such as the long jump — is typed into cell D10 it will return a zero (0).

Entering the MAX IF Nested Formula

The job of each part of the formula is:

• The MAX function finds the highest result for the event chosen.
• The IF function allows us to choose the event by setting a condition using the event names.
• The array formula lets the IF function test for multiple conditions in a single cell, and, when the data meets a condition, the array formula determines what data (event results) the MAX function will examine to find the best result.

Since we are creating both a nested formula and an array formula, we will need to type it directly into a worksheet cell.

Once you have entered the formula, do not press the Enter key on the keyboard or click on a different cell with the mouse as we need to turn the formula into an array formula.

1. Enter the following data into cells D1 to E9 as seen in this image.

2. Type high jump into cell D10. The formula will look at this cell to match it with the events in cells D2 to D7.

3. Select cell E10 which is the location where the formula results will be displayed.

4. Type the following:

   =MAX( IF(D2:D7=D10, E2:E7) )

5. Press the Enter key on the keyboard to create the array formula.

6. Test the formula by finding the best result for the pole vault. Type pole vault into cell D10 and press the Enter key on the keyboard. The formula should return the height of 5.65 meters in cell E10.