Excel MAX IF Formula: Find the Largest Matching Value

Formulas & functions · Updated November 18, 2026

To find the largest value in Excel that meets a condition, use MAXIFS. For example, with categories in A2:A8 and values in B2:B8, =MAXIFS(B2:B8,A2:A8,"East") returns the largest value in column B for rows labeled East. If your Excel version does not support MAXIFS, you can use MAX with IF instead.

What an Excel MAX IF formula does

A MAX IF calculation combines two ideas: MAX finds the largest number in a set, while IF identifies which rows meet a test. The result is the highest numeric value among the rows that pass the test. It is useful for questions such as “What was the biggest sale in the East region?” or “What was the latest payment for this customer?”

In current Excel, MAXIFS is usually the clearest choice. It accepts a range of values to maximize, followed by one or more ranges to check and the criteria those ranges must meet. The function is available in Microsoft 365 and Excel 2019 or later. The formula syntax is the same in current Excel for Windows, Mac, and the web; availability of a function can depend on the version you use.

The term “MAX IF formula” is also used for the older array-formula technique MAX(IF(...)). That approach remains useful when a workbook must work in an older Excel release, but it needs more care. The formulas below show both methods.

Use MAXIFS for one condition

Suppose a small sales table has regions in A2:A8 and sale amounts in B2:B8. The East rows contain sales of 90, 125, and 110; other regions have different amounts. With those ranges and values, enter:

With region names in A2:A8 and sales amounts in B2:B8, this formula returns 125, the largest sale for East:

=MAXIFS(B2:B8,A2:A8,"East")

The first argument, B2:B8, is the range Excel searches for the maximum. The next pair tells Excel to inspect A2:A8 and keep rows whose region is East. The criteria text is in quotation marks because it is typed directly into the formula.

To make the same calculation reusable for different regions, put a region name in D2 and refer to that cell as the criterion. With regions in A2:A8, sales in B2:B8, and the requested region in D2, use:

=MAXIFS($B$2:$B$8,$A$2:$A$8,D2)

If D2 contains East, the result is 125. The dollar signs lock the data ranges if you copy the formula down, while D2 changes to D3, D4, and so on. This is handy for creating a list of maximum sales by region.

Apply multiple conditions

MAXIFS can test more than one condition. Each additional condition is supplied as another range-and-criteria pair. For example, a table might have regions in A2:A9, product names in B2:B9, and sales in C2:C9. To find the largest sale where the region is East and the product is Tea, use:

With regions in A2:A9, products in B2:B9, and sales in C2:C9, this returns 68 if the qualifying East/Tea sales are 42, 68, and 51:

=MAXIFS(C2:C9,A2:A9,"East",B2:B9,"Tea")

All the criteria must be true for a row to qualify: this formula uses AND logic. The maximum is selected only from sales on rows that meet both tests. For an OR requirement, such as the maximum for either East or West, there is no single OR argument in MAXIFS. One straightforward option is to calculate each maximum separately and compare the results with MAX, taking care to handle categories that have no matches.

Criteria can also refer to cells. If E2 contains East and F2 contains Tea, the same layout works with those cell references instead of typed labels. Cell references make it easier to update a report without editing its formula each time.

Use number, date, and wildcard criteria

MAXIFS criteria can include comparison operators such as greater than, less than, or equal to. When an operator is combined with a value stored in a cell, join the operator text to the reference with the ampersand character. For example, suppose dates are in A2:A9, amounts are in C2:C9, and F2 and G2 hold the start and end dates for a period. To find the largest amount on or after the start date but before the end date, use:

With dates in A2:A9, amounts in C2:C9, the inclusive start date in F2, and the exclusive end date in G2, this returns the largest amount within that date interval:

=MAXIFS(C2:C9,A2:A9,">="&F2,A2:A9,"<"&G2)

The end boundary is exclusive, so the formula can cover a whole month by setting G2 to the first day of the next month. Excel stores dates as serial numbers, so make sure the cells contain real Excel dates rather than text that only looks like a date. Format the result as a date if the values being maximized are dates.

For text criteria, MAXIFS supports wildcard characters. An asterisk represents any number of characters, and a question mark represents one character. For instance, a criterion like North* can match text beginning with North. A tilde can be used to treat a wildcard character as a literal character. Wildcards are for text matching; they do not change how Excel compares numeric values.

Use MAX with IF in older Excel

If MAXIFS is unavailable, the traditional alternative is an array formula combining MAX and IF. With region names in A2:A8 and numeric values in B2:B8, the formula is:

=MAX(IF(A2:A8="East",B2:B8,""))

In Microsoft 365 and modern dynamic-array versions of Excel, press Enter. In older Excel versions that require legacy array entry, select the formula cell, type the formula, then press Ctrl+Shift+Enter on Windows. Excel adds braces around a legacy array formula in the formula bar; do not type the braces yourself. On a Mac, the legacy shortcut is typically Command+Shift+Enter.

Here IF supplies values from column B only for rows where column A equals East; the empty text for other rows is ignored by MAX. This returns 125 for the example data. The formula is more difficult to read and can be less convenient to maintain than MAXIFS, especially when there are several criteria. For two conditions, an older array formula can combine tests using multiplication, but each range must line up row by row.

Be careful with a simpler version that leaves out the empty-text result. False results can behave like zero in calculations, which may produce an incorrect answer when all qualifying numbers are negative. Using an empty text result avoids that particular trap because MAX ignores text values in a referenced array. If no rows qualify, both approaches may return zero, so check for a match separately when zero could be mistaken for a genuine maximum.

Choose the right approach

For most current workbooks, choose MAXIFS when you need a maximum subject to one or more conditions. It is direct, supports multiple criteria, and is easier for another person to interpret. Choose MAX(IF(…)) when compatibility with an older Excel version is essential, or when you specifically need an array calculation that MAXIFS cannot express as neatly.

  • Use MAX for the largest value in a range when there is no condition.
  • Use MAXIFS for one or more straightforward conditions in Excel 2019 or later, including Microsoft 365.
  • Use MAX(IF(…)) for a legacy-compatible conditional maximum, entering it as an array formula where required.
  • Use a PivotTable when you need to summarize maximum values across many categories and want an interactive report rather than a single formula result.

In Excel for Windows, insert MAXIFS by typing it in a cell or selecting it from Formulas > More Functions > Statistical. On the Mac, the formula itself works the same way in supported versions; ribbon organization can differ slightly by release. Excel for the web also supports MAXIFS in current versions. If a workbook will be opened in an older desktop release, check compatibility before relying on a newer function.

Common errors and checks

A MAXIFS result of zero does not always mean the true maximum is zero. If no row meets the criteria, MAXIFS returns 0; zero may also be the actual largest qualifying value. This can be ambiguous when qualifying values are negative, because zero is larger than every negative number. Confirm that the criterion actually matches data before interpreting a zero result.

Check these details when the answer looks wrong:

  • Matching range sizes: The max range and every criteria range should have the same number of rows and columns. A2:A8 and B2:B8 align; A2:A8 and B2:B9 do not.
  • Text consistency: Extra spaces, spelling differences, and unexpected characters can prevent a text criterion from matching. Check the source cells, not just how they appear visually.
  • Numbers stored as text: Text that resembles a number may not be treated as a numeric value to maximize. Convert the source data to numbers if necessary.
  • Criteria operators: For a comparison built from a cell reference, join the operator and reference correctly. A typed criterion such as “>100” needs quotation marks; a criterion based on a cell needs concatenation.
  • Dates: Confirm that both the source dates and boundary dates are valid Excel dates. Text dates can cause unexpected matches or no matches.
  • Blank cells and errors: Review the source range for error values and unusual entries. Cleaning or validating the source data often reveals why a result differs from expectations.

Also distinguish the maximum value from the row that contains it. MAXIFS returns the value only; it does not return the associated salesperson, date, or record. If you need the matching label as well, use a lookup method in addition to the maximum calculation, and consider how to handle ties because multiple rows can share the same largest value.

Frequently Asked Questions

Does Excel have a MAX IF function?
There is no worksheet function named MAXIF. Use MAXIFS for conditional maximums in supported Excel versions, or use the array formula MAX(IF(…)) for older versions.

What is the difference between MAX and MAXIFS?
MAX returns the largest number in its argument or range without applying criteria. MAXIFS returns the largest value from a chosen range only among rows that satisfy one or more conditions.

Why does MAXIFS return 0 when there is no match?
Zero is the function’s result when no values meet the criteria. Check that the criterion matches the source data, especially if your real qualifying values could all be negative.

Can MAXIFS use more than one condition?
Yes. Add a criteria range and criterion for each condition. A row is included only when it meets every supplied condition.

Can I use MAXIFS to return a name or other text?
No. MAXIFS returns a maximum value, not the text from the matching row. Use a lookup formula or another approach to retrieve an associated name, and decide how the result should behave if the maximum occurs more than once.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.