Excel INDEX and AVERAGE: How to Combine Them

Formulas & functions · Updated November 18, 2026

Excel has no single function called INDEX AVERAGE; instead, combine INDEX with AVERAGE so Excel averages the value or range selected by INDEX. With the values 10, 20, 30, 40, and 50 in B2:B6, this formula averages the middle three values, 20, 30, and 40, and returns 30: =AVERAGE(INDEX(B2:B6,2):INDEX(B2:B6,4)).

What INDEX and AVERAGE Do

INDEX locates a value or reference at a specified position within a range. The row and column numbers it uses are relative to the selected range, not necessarily the worksheet’s row and column numbers. For example, the second row of B2:B6 is worksheet cell B3. AVERAGE calculates the arithmetic mean of numbers: it adds the numbers and divides by how many numeric values there are.

Used together, INDEX chooses which data AVERAGE should process. This is useful when the relevant row, column, or range may change, such as when a reader selects a month from a drop-down list. For a fixed range, an ordinary AVERAGE formula is usually simpler. INDEX becomes valuable when you want the average to respond to a selection or when you need to identify a range by position.

The order matters: AVERAGE is the outer function, and INDEX is inside it. INDEX does not calculate an average by itself, and AVERAGE does not choose a row or column. Keeping those roles separate makes combined formulas easier to understand and troubleshoot.

Basic Syntax and Position Numbers

In its array form, INDEX takes an array or range, a row number, and optionally a column number. The row and column positions start at 1. If the array is B2:D6, row 1 means worksheet row 2, and column 1 means worksheet column B. This relative numbering is a frequent source of off-by-one mistakes.

When INDEX is used as the argument to AVERAGE, it can return a single value or, in suitable forms, a row or column reference. AVERAGE can then calculate the mean of those numbers. If you use a colon between two INDEX expressions, the expressions can define the start and end references of a range. In that pattern, both endpoints must refer to cells in a compatible range.

For example, in B2:B6, INDEX with a row position of 2 points to B3, while a row position of 4 points to B5. The colon between those references makes B3:B5 the range to average. The formula in the opening example therefore uses the positions within B2:B6, rather than worksheet row numbers 2 and 4.

Average a Range Between Two INDEX Positions

This endpoint technique is handy when the start and end of an average are controlled by positions. Suppose B2:B6 contains 10, 20, 30, 40, and 50. The formula below averages B3:B5. Its result is 30, since (20 + 30 + 40) divided by 3 equals 30.

With 10, 20, 30, 40, and 50 in B2:B6, the following formula uses positions 2 and 4 within that range as the endpoints: =AVERAGE(INDEX(B2:B6,2):INDEX(B2:B6,4)).

A useful detail is that the INDEX calls in this pattern are being used to create references, not merely to display two numbers. The colon is Excel’s range operator. If you accidentally put a comma between the INDEX calls instead, Excel would treat the selected endpoints as separate arguments to AVERAGE; that would average just those two endpoint values, not every value between them.

For a simple average of all five cells, there is no need for INDEX. With those same values in B2:B6, use =AVERAGE(B2:B6); the result is 30. Prefer this direct version when the range is fixed, because it is shorter and easier for someone else to review.

Average a Selected Column

A common dynamic use is to average a column selected by its heading. Imagine headings in B1:D1 named Jan, Feb, and Mar, with five rows of numeric data in B2:D6. Put the heading you want to use in G1. The formula below finds that heading, uses INDEX to select the corresponding data column, and averages its values.

For a concrete example, suppose the values in the March column D2:D6 are 30, 35, 40, 45, and 50, and G1 contains Mar. MATCH finds Mar as the third heading in B1:D1. INDEX then returns the third column of B2:D6, which is D2:D6, and AVERAGE returns 40.

With headings Jan, Feb, and Mar in B1:D1, numeric data in B2:D6, and the requested heading in G1, enter: =AVERAGE(INDEX(B2:D6,0,MATCH(G1,B1:D1,0))).

The zero in INDEX’s row position requests the whole selected column; the column position comes from MATCH. The final zero in MATCH requests an exact match, so the text in G1 must match a heading. If the heading is misspelled or absent, MATCH returns an error, and that error carries through the formula. Use consistent labels and avoid accidental extra spaces in the headings.

Average a Selected Row

The same idea can select a row instead of a column. For example, a table might list regions in A2:A6 and monthly values in B2:D6. If G1 contains a region name, MATCH can find its position in A2:A6, and INDEX can return that row of monthly numbers. AVERAGE then calculates the row’s mean.

Assume A2:A6 contains North, South, East, West, and Central in that order. Suppose the East row, B4:D4, contains 20, 30, and 40, and G1 contains East. The selected row averages to 30.

With region names in A2:A6, the three month values in B2:D6, and the region to find in G1, use: =AVERAGE(INDEX(B2:D6,MATCH(G1,A2:A6,0),0)).

Here, the row number is supplied by MATCH and the zero column number tells INDEX to return that whole row. This pattern is useful for a summary cell that changes when the requested region changes. It assumes every selected row contains numeric values in the cells being averaged; blanks and text have the effects described below.

Choosing Between INDEX, AVERAGEIF, and Other Options

Use INDEX with AVERAGE when you need to select data by position or by a matching row or column label, then average the selected numbers. It is not a criteria-based averaging function by itself. If your actual question is to average values that meet a condition, AVERAGEIF or AVERAGEIFS may be a clearer choice.

For instance, suppose B2:B6 contains 10, 20, 30, 40, and 50. To average only numbers greater than 30, use AVERAGEIF rather than INDEX: =AVERAGEIF(B2:B6,">30"). The qualifying values are 40 and 50, so the result is 45. AVERAGEIFS is intended for applying multiple criteria. Choose based on the task: selection by position points toward INDEX, while selection by condition points toward AVERAGEIF or AVERAGEIFS.

If the data is arranged as a formal Excel Table, structured references can make a direct average easier to read, particularly when the table grows. For example, a direct average of a known table column may be enough; INDEX is not automatically better simply because a formula can use it. A well-chosen simple formula is often easier to maintain than a more flexible formula the workbook does not need.

Common Errors and Data Details

When a combined formula returns an unexpected result, check the selected range first. A position in INDEX is counted from the top-left cell of the array. If the array begins at B2, a row position of 1 selects B2, not B1. In a two-dimensional array, the column position is counted from the array’s left edge in the same way.

  • #REF!: A row or column position is outside the dimensions of the selected array, or a reference used by the formula has become invalid.
  • #N/A: MATCH could not find the requested label. Check the lookup text, spaces, and whether the label exists in the lookup range.
  • #VALUE! or another error: The selected cells may contain an error, or part of the formula may use an incompatible argument. Errors in a selected range can affect AVERAGE’s result.
  • An unexpectedly high or low mean: Confirm that the intended rows and columns were selected and that zeros are genuine values rather than placeholders.

AVERAGE ignores empty cells and text in referenced cells, but it includes zero as a number. Thus, a cell containing 0 lowers the result, while a blank cell does not count as a numeric observation. A cell that displays a dash might contain text, a zero formatted to display as a dash, or a formula result; its underlying value matters. Inspect the cell contents and number format before treating displayed marks as missing data.

Also remember that AVERAGE does not automatically exclude hidden or filtered-out rows when you give it a normal range. If the requirement is to average only visible rows, a plain INDEX-and-AVERAGE combination may not express that requirement. Choose a method designed for visible data, or explicitly filter the values you want included, and verify the result against a small manual sample.

How to Build and Check the Formula

Creating the formula in stages makes it much easier to verify than typing a long expression all at once. First confirm that the source data and labels are arranged consistently, then test the lookup and selection parts separately.

  1. Identify the exact cells containing the numeric data and note whether you are selecting a row, a column, or a segment of one column.
  2. Check the INDEX position using a small example. Count from the top-left of its array, not from the worksheet headings.
  3. If you are locating a label, confirm that MATCH uses the correct lookup range and an exact-match setting where appropriate.
  4. Wrap the selected reference in AVERAGE, then change the selection input and confirm that the result changes as expected.
  5. For a quick check, calculate the selected values’ sum and count separately or verify a short example by hand.

For a workbook shared with other people, label the input cell clearly and keep the lookup headings consistent. A formula can be mathematically correct but still confusing if no one knows what the selection cell represents. If users may enter a label manually, a drop-down list can reduce typing errors; make sure its available choices come from the same headings that MATCH searches.

Frequently Asked Questions

Is there an Excel function named INDEX AVERAGE?
No. INDEX and AVERAGE are separate worksheet functions. They can be nested so INDEX selects the data and AVERAGE calculates its mean, but there is no single built-in function with the combined name.

Can INDEX return an entire row or column?
Yes. In the array form, using 0 for the row number requests the selected column, and using 0 for the column number requests the selected row. Those results can be used by functions such as AVERAGE in supported formula contexts.

Why does my INDEX and AVERAGE formula show #N/A?
If the formula uses MATCH, #N/A usually means the lookup value was not found. Check that the label is in the searched range and that the lookup value does not contain an unnoticed space or spelling difference. INDEX can also return an error if its position is outside the selected array.

Does AVERAGE count blank cells as zero?
No. AVERAGE ignores blank cells in a referenced range, but it does include cells whose value is zero. This distinction can materially change the result when a dataset uses zeros to mean missing information.

Should I use INDEX and AVERAGE or AVERAGEIF?
Use INDEX with AVERAGE when you need to choose a row, column, or positional range and average it. Use AVERAGEIF when the values should be included because they meet one condition, and AVERAGEIFS when multiple conditions apply. The simplest function that matches the task is usually the easiest to audit.

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.