Formula Errors

How to Fix Excel AGGREGATE and MATCH Formula Errors for Row Maximums

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to match an occupation across two Excel tables and return the maximum value from four specific columns in the matched row, ignoring any errors, but the current formula yields incorrect results.

Product: Excel
Device & OS: not provided
Scenario: Looking up a value across tables and calculating the maximum value from multiple columns in the matching row.
Observed behavior: The current AGGREGATE, INDEX, and MATCH formula returns maximum values by column instead of calculating the maximum across the four cells in the specific matched row.

Before you start

Ensure that your table names and column headers in the formula exactly match the structure of your Excel workbook before troubleshooting range references.

Solution 1Recommended

Correct the Range Orientation with INDEX and MATCH

Adjust the INDEX array orientation to pull the correct row values before calculating the maximum with AGGREGATE.

The issue usually stems from an incorrect range orientation where Excel calculates the maximum values by column rather than across the specific row.

By adjusting how INDEX retrieves the data array, you can force AGGREGATE to evaluate the four columns of the matched row correctly. Wrapping the function in IFERROR will also ensure that any unresolved matches return a blank cell instead of a visible error code.

Identify your target tables

Locate the table containing the occupations (e.g., Table20) and the specific columns you want to extract the maximum value from (e.g., Current Average Wage to 2-Year Earning Potential).

Construct the MATCH function

Use MATCH to find the row number of the occupation. Example: MATCH([@[Occ Name]], Table20[Occupations], 0).

Extract the row using INDEX

Place the MATCH formula inside an INDEX function that references your four target columns. Leave the row argument empty or properly aligned so INDEX returns the entire array for that matched row.

Apply AGGREGATE and IFERROR

Wrap the INDEX function inside AGGREGATE(4,6, ...) to find the MAX (function 4) and ignore errors (option 6). Finally, wrap everything in IFERROR to handle missing matches: =IFERROR(AGGREGATE(4,6,INDEX(Table20[[Current Average Wage]:[2-Year Earning Potential]],,MATCH([@[Occ Name]],Table20[Occupations],0))),"").

Formula Tip: Using option 6 in the AGGREGATE function is crucial because it automatically ignores any #DIV/0! or #N/A errors present within the matched row's data cells.

WPS Spreadsheet Formula Support

Easily Manage Complex Formulas with WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced array formulas, including AGGREGATE, INDEX, and MATCH. You can build, debug, and execute complex data analysis seamlessly in a lightweight environment.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open your existing .xlsx file.
2. Enter the formula: Click on the target cell and paste the corrected AGGREGATE and MATCH formula.
3. Verify results: Press Enter to apply the formula and drag the fill handle down to apply it to the rest of the column.

Fully compatible with Microsoft Excel formulas and functions.Built-in error checking and formula evaluation tools.Lightweight application with fast calculation speeds for large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my AGGREGATE formula return a #VALUE! error?

This usually happens if the array provided to the AGGREGATE function contains text strings that cannot be ignored, or if the function number used requires a 'k' argument which was not provided. Ensure you use function 4 for MAX.

What does the 6 mean in the AGGREGATE function?

The number 6 is the options argument in the AGGREGATE function, which tells the spreadsheet software to ignore error values within the referenced array.

Can I use MAXIFS instead of AGGREGATE for this?

MAXIFS evaluates criteria across ranges but doesn't easily return the maximum value across multiple columns in a single row without restructuring your data. AGGREGATE combined with INDEX is much more effective for row-wise maximum calculations.

Excel & Data Problems