Excel MAX IF Array Formula

Combine the MAX and IF Functions in an Array Formula

A templated spreadsheet in Excel.

Cindy Grigg, Courtesy of Microsoft

This tutorial example uses a MAX IF array formula to find the best (highest) result for two track and field events – the high jump and pole vault.

The nature of the formula permits us to search for multiple results simply by changing the search criterion – in this case the event name.

The job of each part of the formula is:

  • the 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 condition is met, the array formula determines what data (event results) the MAX function will examine to find the best result.

CSE Formulas

Array formulas are created by pressing the Ctrl, Shift, and Enter keys on the keyboard at the same time once the formula has been typed in.

Because of the keys pressed to create the array formula, they are sometimes referred to as CSE formulas.

MAX IF Nested Formula Syntax and Arguments

The syntax for the MAX IF formula is:

=MAX( IF ( logical_test, value_if_true, value_if_false ) )

  • Since the IF function is nested 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 will be omitted since it is not needed 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 the formula will return a zero ( 0 ).

Excel's MAX IF Array Formula Example

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

     Event Results Event Height (m) High Jump 2.10 High Jump 2.23 High Jump 1.97 Pole Vault 3.58 Pole Vault 5.65 Pole Vault 5.05 Event Best Result (m) 
  2. In cell D10 type " high jump " (no quotes). The formula will look in this cell to find which of the events we want it to find the best result for.

Entering the MAX IF Nested Formula

Since we are creating both a nested formula and an array formula, we will need to type the entire formula into a single 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. Click on cell E10 - the location where the formula results will be displayed.

  2. Type the following:

= MAX ( IF( D3:D8=D10, E3:E8 ) )

Creating the Array Formula

  1. Press and hold down the Ctrl and Shift keys on the keyboard.

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

  3. The answer 2.23 should appear in cell E10 since this is the best (largest) height for the high jump.

  4. The complete array formula

{ = MAX ( IF( D3:D8=D10, E3:E8 ) ) }

  1. Can be seen in the formula bar above the worksheet.

Test the Formula

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.