Excel MAX IF Array Formula

Combine the MAX and IF functions in an array formula

Businessman working on spreadsheet

Thomas Barwick / Getty Images

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.

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.

These instructions apply to Microsoft Excel 2019, 2016, 2010, 2007, and Excel for Office 365.

CSE Formulas

Array formulas are created by pressing the Ctrl, Shift, and Enter keys on the keyboard in succession once you've typed in the formula.

Screenshot of Excel showing example data

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

MAX IF Nested Formula Syntax and Arguments

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).
Screenshot of Excel showing syntax

Excel's MAX IF Array Formula Example

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

  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.

Entering the MAX IF Nested Formula

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.

Screenshot of Pole Vault data
  1. Click on cell E10 which is the location where the formula results will be displayed.

    You'll need to enable editing in a cell by going to File > Options > Advanced. Under Editing options, select Allow editing directly in cells.

  2. Type the following:

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

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

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.