INDEX function in Excel - 6 most efficient uses

INDEX array form

The INDEX array form returns the value of a certain element in a range or array based on the row and column numbers you specify.

• array - is a range of cells, named range, or table.
• row_num - is the row number in the array from which to return a value. If row_num is omitted, column_num is required.
• column_num - is the column number from which to return a value. If column_num is omitted, row_num is required.

For example, the formula =INDEX(A1:D6, 4, 3) returns the value at the intersection of the 4^th row and 3^rd column in range A1:D6, which is the value in cell C4.

To get an idea of how the INDEX formula works on real data, please have a look at the following example:

Instead of entering the row and column numbers in the formula, you can supply the cell references to get a more universal formula: =INDEX($B$2:$D$6, G2, G1)

So, this INDEX formula returns the number of items exactly at the intersection of the product number specified in G2 (row_num) and week number entered in cell G1 (column_num).

INDEX reference form

The reference form of the Excel INDEX function returns the cell reference at the intersection of the specified row and column.

• reference - is one or several ranges.

  If you are entering more than one range, separate the ranges by commas and enclose the reference argument in parentheses, for example (A1:B5, D1:F5).

  If each range in reference contains only one row or column, the corresponding row_num or column_num argument is optional.

• row_num - the row number in the range from which to return a cell reference, it's similar to the array form.
• column_num - the column number from which to return a cell reference, also works similarly to the array form.
• area_num - an optional parameter that specifies which range from the reference argument to use. If omitted, the INDEX formula will return the result for the first range listed in reference.

For example, the formula =INDEX((A2:D3, A5:D7), 3, 4, 2) returns the value of cell D7, which is at the intersection of the 3^rd row and 4^th column in the second area (A5:D7).

Source data

All of our INDEX formulas (except for the last one), we will use the below data. For convenience purposes, it is organized in a table named SourceData.

The use of tables or named ranges can make formulas a bit longer, but it also makes them significantly more flexible and better readable. To adjust any INDEX formula for your worksheets, you need only to modify a single name, and this fully makes up for a longer formula length.

Of course, nothing prevents you from using usual ranges if you want to. In this case, you simply replace the table name SourceData with the appropriate range reference.

1. Getting the N^th item from the list

This is the basic use of the INDEX function and a simplest formula to make. To fetch a certain item from the list, you just write =INDEX(range, n) where range is a range of cells or a named range, and n is the position of the item you want to get.

When working with Excel tables, you can select the column using the mouse and Excel will pull the column's name along with the table's name in the formula:

To get a value of the cell at the intersection of a given row and column, you use the same approach with the only difference that you specify both - the row number and the column number. In fact, you already saw such a formula in action when we discussed INDEX array form.

And here's one more example. In our sample table, to find the 2^nd biggest planet in the Solar system, you sort the table by the Diameter column, and use the following INDEX formula:

=INDEX(SourceData, 2, 3)

• Array is the table name, or a range reference, SourceData in this example.
• Row_num is 2 because you are looking for the second item in the list, which is in the 2^nd
• Column_num is 3 because Diameter is the 3^rd column in the table.

If you want to return the planet's name rather than diameter, change column_num to 1. And naturally, you can use a cell reference in the row_num and/or column_num arguments to make your formula more versatile, as demonstrated in the screenshot below:

2. Getting all values in a row or column

Apart from retrieving a single cell, the INDEX function is able to return an array of values from the entire row or column. To get all values from a certain column, you have to omit the row_num argument or set it to 0. Likewise, to get the entire row, you pass empty value or 0 in column_num.

Such INDEX formulas can hardly be used on their own, because Excel is unable to fit the array of values returned by the formula in a single cell, and you would get the #VALUE! error instead. However, if you use INDEX in conjunction with other functions, such as SUM or AVERAGE, you will get awesome results.

For example, you could use the following formula to calculate the average planet temperature in the Solar system:

=AVERAGE(INDEX(SourceData, , 4))

In the above formula, the column_num argument is 4 because Temperature in the 4^th column in our table. The row_num parameter is omitted.

In a similar manner, you can find the minimum and maximum temperatures:

=MAX(INDEX(SourceData, , 4))

=MIN(INDEX(SourceData, , 4))

And calculate the total planet mass (Mass is the 2^nd column in the table):

=SUM(INDEX(SourceData, , 2))

From practical viewpoint, the INDEX function in the above formula is superfluous. You can simply write =AVERAGE(range) or =SUM(range) and get the same results.

When working with real data, this feature may prove helpful as part of more complex formulas you use for data analysis.

3. Using INDEX with other functions (SUM, AVERAGE, MAX, MIN)

From the previous examples, you might be under an impression that an INDEX formula returns values, but the reality is that it returns a reference to the cell containing the value. And this example demonstrates the true nature of the Excel INDEX function.

Since the result of an INDEX formula is a reference, we can use it within other functions to make a dynamic range. Sounds confusing? The following formula will make everything clear.

Suppose you have a formula =AVERAGE(A1:A10) that returns an average of the values in cells A1:A10. Instead of writing the range directly in the formula, you can replace either A1 or A10, or both, with INDEX functions, like this:

=AVERAGE(A1 : INDEX(A1:A20,10))

Both of the above formulas will deliver the same result because the INDEX function also returns a reference to cell A10 (row_num is set to 10, col_num omitted). The difference is that the range is the AVERAGE / INDEX formula is dynamic, and once you change the row_num argument in INDEX, the range processed by the AVERAGE function will change and the formula will return a different result.

Apparently, the INDEX formula's route appears overly complicated, but it does have practical applications, as demonstrated in the following examples.

4. INDEX formula to create dynamic ranges and drop-down lists

As it often happens, when you start organizing data in a worksheet, you may not know how many entries you will eventually have. It's not the case with our planets table, which seems to be complete, but who knows...

Anyway, if you have a changing number of items in a given column, say from A1 to An, you may want to create a dynamic named range that includes all cells with data. At that, you want the range to adjust automatically as you add new items or delete some of the existing ones. For example, if you currently have 10 items, your named range is A1:A10. If you add a new entry, the named range automatically expands to A1:A11, and if you change your mind and delete that newly added data, the range automatically reverts to A1:A10.

The main advantage of this approach is that you do not have to constantly update all formulas in your workbook to ensure they refer to correct ranges.

One way to define a dynamic range is using Excel OFFSET function:

=OFFSET(Sheet_Name!$A$1, 0, 0, COUNTA(Sheet_Name!$A:$A), 1)

Another possible solution is to use Excel INDEX together with COUNTA:

=Sheet_Name!$A$1:INDEX(Sheet_Name!$A:$A, COUNTA(Sheet_Name!$A:$A))

In both formulas, A1 is the cell containing the first item of the list and the dynamic range produced by both formulas will be identical.

The difference is in the approaches. While the OFFSET function moves from the starting point by a certain number of rows and/or columns, INDEX finds a cell at the intersection of a particular row and column. The COUNTA function, used in both formulas, gets the number of non-empty cells in the column of interest.

In this example, there are 9 non-blank cells in column A, so COUNTA returns 9. Consequently, INDEX returns $A$9, which is the last used cell in column A (usually INDEX returns a value, but in this formula, the reference operator (:) forces it to return a reference). And because $A$1 is our starting point, the final result of the formula is the range $A$1:$A$9.

The following screenshot demonstrates how you can use such Index formula to create a dynamic drop-down list.

You can also use the INDEX function to create dependent drop-down lists and the following tutorial explains the steps: Making a cascading drop-down list in Excel.

5. Powerful Vlookups with INDEX / MATCH

Performing vertical lookups - this is where the INDEX function truly shines. If you have ever tried using Excel VLOOKUP function, you are well aware of its numerous limitations, such as inability to pull values from columns to the left of the lookup column or 255 chars limit for a lookup value.

The INDEX / MATCH liaison is superior to VLOOKUP in many respects:

• No problems with left vlookups.
• No limit to the lookup value size.
• No sorting is required (VLOOKUP with approximate match does require sorting the lookup column in ascending order).
• You are free to insert and remove columns in a table without updating every associated formula.
• And the last but not the least, INDEX / MATCH does not slow down your Excel like multiple Vlookups do.

You use INDEX / MATCH in the following way:

For example, if we flip our source table so that Planet Name becomes the right-most column, the INDEX / MATCH formula still fetches a matching value from the left-hand column without a hitch.

For more tips and formula example, please see the Excel INDEX / MATCH tutorial.

6. Excel INDEX formula to get 1 range from a list of ranges

Another smart and powerful use of the INDEX function in Excel is the ability to get one range from a list of ranges.

Suppose, you have several lists with a different number of items in each. Believe me or not, you can calculate the average or sum the values in any selected range with a single formula.

First off, you create a named range for each list; let it be PlanetsD and MoonsD in this example:

I hope the above image explains the reasoning behind the ranges' names : ) BTW, the Moons table is far from complete, there are 176 known natural moons in our Solar System, Jupiter alone has 63 currently, and counting. For this example, I picked random 11, well... maybe not quite random - moons with the most beautiful names : )

Please excuse the digression, back to our INDEX formula. Assuming that PlanetsD is your range 1 and MoonsD is range 2, and cell B1 is where you put the range number, you can use the following Index formula to calculate the average of values in the selected named range:

=AVERAGE(INDEX((PlanetsD, MoonsD), , , B1))

Please pay attention that now we are using the Reference form of the INDEX function, and the number in the last argument (area_num) tells the formula which range to pick.

In the screenshot below, area_num (cell B1) is set to 2, so the formula calculates the average diameter of Moons because the range MoonsD comes 2^nd in the reference argument.

If you work with multiple lists and don't want to bother remembering the associated numbers, you can employ a nested IF function to do this for you:

=AVERAGE(INDEX((PlanetsD, MoonsD), , , IF(B1="planets", 1, IF(B1="moons", 2))))

In the IF function, you use some simple and easy-to-remember list names that you want your users to type in cell B1 instead of numbers. Please keep this in mind, for the formula to work correctly, the text in B1 should be exactly the same (case-insensitive) as in the IF's parameters, otherwise your Index formula will throw the #VALUE error.

To make the formula even more user-friendly, you can use Data Validation to create a drop-down list with predefined names to prevent spelling errors and misprints:

Finally, to make your INDEX formula absolutely perfect, you can enclose it in the IFERROR function that will prompt the user to choose an item from the drop-down list if no selection has been made yet:

=IFERROR(AVERAGE(INDEX((PlanetsD, MoonsD), , , IF(B1="planet", 1, IF(B1="moon", 2)))), "Please select the list!")