Excel MAX with NA: Formula to Ignore Errors

Formulas & functions · Updated November 22, 2026

The direct answer: to find the maximum value in a range while ignoring #N/A errors, use the array formula =MAX(IF(ISNA(range),"",range)) entered with Ctrl+Shift+Enter in older Excel, or the dynamic array formula =MAX(IFNA(range,"")) in Excel 365. For a simpler non-array alternative, use =AGGREGATE(4,6,range), where 4 means MAX and 6 ignores error values.

Why #N/A Errors Break MAX

Excel’s MAX function is designed to ignore text and logical values, but it does not ignore error values. If any cell in the range contains #N/A, #VALUE!, #DIV/0!, or any other error, MAX returns that same error. This is a common frustration when working with VLOOKUP, XLOOKUP, or MATCH results that return #N/A for missing items.

The AGGREGATE Function (Simplest Method)

If you’re using Excel 2010 or later, AGGREGATE is the cleanest solution. It combines the functionality of SUBTOTAL with the ability to ignore errors, hidden rows, and other conditions.

Syntax: =AGGREGATE(function_num, options, array)

  • function_num: 4 for MAX, 5 for MIN, 1 for AVERAGE, etc.
  • options: 6 to ignore error values, 5 to ignore hidden rows, 7 to ignore both.
  • array: the range to evaluate.

Example: With values in A2:A10, where A5 contains #N/A, the formula =AGGREGATE(4,6,A2:A10) returns the largest number ignoring the error. If the range contains only errors, it returns #NUM!.

Array Formula with IF and ISNA (Works in All Versions)

For older Excel versions (2007 and earlier), you must use an array formula. The classic approach replaces errors with an empty string, which MAX ignores.

With values in A2:A10, enter: =MAX(IF(ISNA(A2:A10),"",A2:A10))

Then press Ctrl+Shift+Enter (not just Enter). Excel will wrap the formula in curly braces {} in the formula bar. On a Mac, press Cmd+Shift+Enter.

If you have other error types besides #N/A, use ISERROR instead of ISNA: =MAX(IF(ISERROR(A2:A10),"",A2:A10)).

Using IFNA in Excel 365 and 2021

In Excel 365 and Excel 2021, dynamic arrays allow you to enter formulas normally without Ctrl+Shift+Enter. The IFNA function specifically handles #N/A errors:

=MAX(IFNA(A2:A10,""))

This works because IFNA returns an empty string for each #N/A in the range, and MAX ignores text. For all error types, use =MAX(IFERROR(A2:A10,"")).

Comparison of Methods

Method Excel Version Pros Cons
AGGREGATE 2010+ Simple, no array entry, ignores all errors Not available in older versions
Array IF(ISNA) All Works everywhere Requires Ctrl+Shift+Enter, can be slow on large ranges
IFNA/IFERROR 365/2021 Easy, dynamic arrays Only in newer versions

Common Problems and Solutions

  • Formula returns 0 instead of a number: If all values are errors, MAX with empty strings returns 0. To avoid this, use =IFERROR(MAX(IFNA(A2:A10,"")),"No valid data").
  • Array formula not working: Forgetting to press Ctrl+Shift+Enter will cause #VALUE!. Re-enter and press the right key combo.
  • Ignoring hidden rows: Use AGGREGATE(4,5,A2:A10) to ignore manually hidden rows.
  • Large data sets: Array formulas over thousands of cells can slow down Excel. Prefer AGGREGATE or a helper column.

Alternative: Helper Column

If you prefer not to use array formulas, add a helper column that cleans errors. For example, in B2 enter =IF(ISNA(A2),"",A2) and copy down. Then use =MAX(B2:B10). This is transparent and works in all versions, but it adds a column to your sheet.

Frequently Asked Questions

How do I ignore #N/A in MAXIFS?

MAXIFS (Excel 2019+) does not ignore errors by default. You can combine it with IF: =MAX(IF(ISNA(A2:A10),"",A2:A10)) with Ctrl+Shift+Enter, or use AGGREGATE with a condition: =AGGREGATE(4,6,IF(criteria_range=criteria,value_range)) as an array formula.

Does MAX ignore text and logical values?

Yes, MAX ignores text, logical values (TRUE/FALSE), and empty cells. It only fails when an error value is present.

What if I want to ignore only #N/A but not other errors?

Use ISNA instead of ISERROR in the array formula, or IFNA in Excel 365. For AGGREGATE, you cannot specify only #N/A; it ignores all errors.

Can I use MAX with VLOOKUP results that may be #N/A?

Yes. Wrap the VLOOKUP in IFNA: =MAX(IFNA(VLOOKUP(range,...),"")) in Excel 365, or use AGGREGATE on the range of VLOOKUP results.

Why does my formula return #NUM! when all cells are errors?

AGGREGATE returns #NUM! if the array contains no numbers. Use IFERROR around it to display a custom message.

Final Tips

Always test your formula with a small sample that includes an error. For financial or critical data, double-check that ignoring errors is appropriate—sometimes you want to know about errors rather than hide them. If you need to audit, keep the original data intact and use a separate column for the cleaned version.

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.