Function Problems

How to Find the Region with the Highest Average Using INDEX MATCH in Excel

Emma Brown Oct 22, 2026 869 views

Question details

The user wants to find and extract the name of the region that has the highest average number of beneficiaries for a specific activity (e.g., Dance) using advanced formulas.

How to Find the Region with the Highest Average Beneficiaries in Excel

Product: Excel
Device & OS: not provided
Scenario: Analyzing regional dataset to identify the top-performing area based on a conditional average value.
Observed behavior: The formula successfully evaluates the conditional average for each region, identifies the maximum value, and returns the exact text name of the matching region.

Before you start

Ensure your dataset is organized in structured columns and that you have defined Named Ranges for your data (such as Region, Beneficiaries, and Activity) via the Formulas tab to make the formula easier to write and maintain.

Solution 1Recommended

Use the LET Function for Microsoft 365 and Excel 2021

This is the most efficient and recommended method for modern Excel versions, as it calculates the conditional averages once and stores them in a local variable, reducing calculation time.

The LET function allows you to assign names to calculation results. By defining the AVERAGEIFS array as a variable 'avg', you avoid writing the same complex formula twice inside the MATCH function.

Define Named Ranges

Go to the Formulas tab on the ribbon, click Name Manager, and create defined ranges for 'Region', 'Beneficiaries', and 'Activity'.

Enter the LET formula

Select the cell where you want the result to appear and type the following formula: =LET(avg,AVERAGEIFS(Beneficiaries,Activity,"Dance",Region,Region),INDEX(Region,MATCH(MAX(avg),avg,0)))

Execute the calculation

Press Enter to execute the formula. Excel will output the name of the region with the highest average beneficiaries for the Dance activity.

Use the LET Function for Microsoft 365 and Excel 2021

Optimization Tip: Using the LET function drastically improves worksheet performance on large datasets because the AVERAGEIFS calculation is only processed once.

Solution 2

Use Standard INDEX, MATCH, MAX, and AVERAGEIFS Nested Formula

For older versions of Excel (2019 and earlier) that do not support the LET function, you can nest the AVERAGEIFS formula inside the MATCH and MAX functions to achieve the same result.

Advanced Formulas in WPS Office

Solve Complex Data Problems with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions, the modern LET function, AVERAGEIFS, INDEX, and MATCH. You can calculate highest regional averages just as you would in Microsoft Excel with seamless compatibility.

1. Open your dataset: Launch WPS Spreadsheet and open your existing data workbook.
2. Define Name Ranges: Select your data columns, navigate to the Formulas tab, and click Name Manager to define your ranges.
3. Input the array formula: Type the exact same =LET(...) or nested INDEX MATCH formula to find the highest average region.
4. View instant results: Press Enter to instantly process your calculated results without any syntax modifications.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Supports modern functions like LET, dynamic arrays, and XLOOKUP.Lightweight, fast execution on large datasets, and free to use.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #NAME? error?

This usually happens if you are using the LET function in an older version of Excel that does not support it (Excel 2019 or earlier). If you encounter this error, switch to the standard nested INDEX and MATCH formula provided in the second solution.

Can I find the region with the lowest average instead?

Yes, you can easily modify the formula. Simply replace the MAX function with the MIN function within your LET or nested formula to locate the region with the lowest average beneficiaries.

How do I add multiple conditions instead of just 'Dance'?

The AVERAGEIFS function is designed for multiple criteria. Simply append another criteria range and criteria pair, such as a Date column and a specific date range, directly inside the AVERAGEIFS syntax parameters.

Excel & Data Problems