by Svetlana Cheusheva, updated on
In this tutorial, you will find a number of formula examples that demonstrate the most efficient uses of INDEX in Excel.
Of all Excel functions whose power is often underestimated and underutilized, INDEX would definitely rank somewhere in the top 10. In the meantime, this function is smart, supple and versatile.
So, what is the INDEX function in Excel? Essentially, an INDEX formula returns a cell reference from within a given array or range. In other words, you use INDEX when you know (or can calculate) the position of an element in a range and you want to get the actual value of that element.
This may sound a bit trivial, but once you realize the real potential of the INDEX function, it could make crucial changes to the way you calculate, analyze and present data in your worksheets.
There are two versions of the INDEX function in Excel - array form and reference form. Both forms can be used in all versions of Microsoft Excel 365 - 2003.
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.
For example, the formula
=INDEX(A1:D6, 4, 3) returns the value at the intersection of the 4th row and 3rd 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).
Tip. The use of absolute references ($B$2:$D$6) instead of relative references (B2:D6) in the array argument makes it easier to copy the formula to other cells. Alternatively, you can convert a range to a table (Ctrl + T) and refer to it by the table name.
The reference form of the Excel INDEX function returns the cell reference at the intersection of the specified row and column.
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.
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 3rd row and 4th column in the second area (A5:D7).
Both of the INDEX formulas we've discussed so far are very simple and only illustrate the concept. Your real formulas are likely to be far more complex than that, so let's explore a few most efficient uses of INDEX in Excel.
Perhaps there aren't many practical uses of Excel INDEX by itself, but in combination with other functions such as MATCH or COUNTA, it can make very powerful formulas.
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.
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 2nd biggest planet in the Solar system, you sort the table by the Diameter column, and use the following INDEX formula:
=INDEX(SourceData, 2, 3)
Arrayis the table name, or a range reference, SourceData in this example.
Row_numis 2 because you are looking for the second item in the list, which is in the 2nd
Column_numis 3 because Diameter is the 3rd 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:
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 4th 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 2nd column in the table):
=SUM(INDEX(SourceData, , 2))
From practical viewpoint, the INDEX function in the above formula is superfluous. You can simply write
=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.
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.
Let's say you want to know the average diameter of the N biggest planets in our system. So, you sort the table by Diameter column from largest to smallest, and use the following Average / Index formula:
=AVERAGE(C5 : INDEX(SourceData[Diameter], B1))
In case you want to define the upper-bound and lower-bound items in your formula, you just need to employ two INDEX functions to return the first and the last item you want.
For example, the following formula returns the sum of values in the Diameter column between the two items specified in cells B1 and B2:
=SUM(INDEX(SourceData[Diameter],B1) : INDEX(SourceData[Diameter], B2))
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:
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.
Tip. The easiest way to create an expandable dynamic data validation list is dropdown from a table. In this case, you won't need any complex formulas since Excel tables are dynamic ranges per se.
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.
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:
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.
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 2nd 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!")
This is how you use INDEX formulas in Excel. I am hopeful these examples showed you a way to harness the potential of the INDEX function in your worksheets. Thank you for reading!
Table of contents