The Excel INDEX function is a treasure trove of functionality, but most of us only know one way to use it. In this post I want to expose some lesser known quirks and ways it can be used.
- If the reference/array is a single row, you can put the col_num in the row_num argument’s position
- INDEX can return a cell or range reference
- 0 or skipping the row/column argument will return the whole row/column
- INDEX can be used either side of the colon, space or comma operators
- INDEX can return non-contiguous areas
Let’s start by quickly revisiting the syntax for the INDEX function. It has two syntax options:
The first version takes an array of values, and the other takes a reference to a cell, or range of cells. You don’t choose which syntax to use, Excel will decide that based on the inputs.
The output can be a single value, an array of values or a reference to a cell or range of cells. The output will depend on the context of the formula. For example, whether the formula is entered in a single cell, nested inside another function, array entered etc.
Watch the Excel INDEX Function Video
Download the Excel INDEX Function Workbook
Enter your email address below to download the sample workbook.
1. Use row_num for col_num when reference/array is a single row
The formula in cell C16 looks up cells C7:F7 i.e. a row and returns the value from column number 2.
Remember the syntax for the INDEX function relevant to this example is:
=INDEX(array, row_num, [column_num])
And the formula above is:
=INDEX(C7:F7,2)
Notice the col_num argument is in the row_num argument’s position. When the reference is a single row, you can simply specify the column number in the row_num argument.
2. INDEX Function Can Return a Reference
The formula in cell C16 below looks up the value for West, which is in row 4 and Q2 which is in column 2:
Note: Most of us would use INDEX with MATCH to return the row_num and column_num arguments, but I want to keep this simple for the next example.
Depending on the formula, the return value of INDEX may be a value, as we saw in the previous examples, or a cell or range reference. In cell C24 (image below) INDEX returns a cell reference, D9, and because I have dynamic arrays, the results spill to the cells below:
Excel 2019 and Earlier Notes:
- If you have Excel 2019 or earlier, you first select 4 cells, then enter the formula by pressing CTRL+SHIFT+ENTER.
- If you entered the formula in Excel 2019 or earlier without pressing CTRL+SHIFT+ENTER, Excel would only show you the first result i.e. 830 because it wouldn’t have enough cells to display all the results returned by the formula.
If you wrap the above formula in SUM (in any version of Excel) it will add the 4 cells like so:
We can see INDEX evaluates to a cell reference in the Evaluate Formula dialog box:
3. Return a Whole Column or Row
If you place a zero in or omit the row_num or column_num arguments, INDEX will return the whole column or row, respectively.
In the example below in cell C16 you can see the row_num argument is zero, and INDEX has spilled the results of the whole column (D6:D9) to the cells below:
Similarly, in the formula below, the row_num argument has been omitted, and again INDEX returns the whole column, which is then summed to return the total, 3,239:
The same rule applies with the column_num argument as you can see in the examples below:
Of course, zeros in both the row and column numbers or omitting these arguments altogether will return the whole range specified in the array/reference argument:
4. Operators
In example 2 we saw INDEX return a cell reference. This ability makes it perfect for returning dynamic named ranges. And if you know me then you’ll probably have seen me use INDEX for this before.
Colon Range Operator
The INDEX function can sit either side of the colon operator like so:
When teamed up with MATCH (see below) it can return a dynamic range that can adapt to changes in the selected quarters in cells C15 and C16:
=SUM( INDEX(C6:F9,3, MATCH(C15,C5:F5,0) ) : INDEX(C6:F9,4, MATCH(C16,C5:F5,0) ) )
Comma Union Operator
INDEX can also be used either side of the union operator, the comma. In the example below it is used to return two quarters, which are then summed:
When MATCH replaces the hard-keyed column references, it becomes dynamic:
=SUM(INDEX(C6:F9,0,MATCH(C24,C5:F5,0)),INDEX(C6:F9,0,MATCH(C25,C5:F5,0)))
Space Intersect Operator
The intersect operator returns the value at the intersection of two ranges.
Again, we can team INDEX with MATCH to make the formula dynamic:
=INDEX(C6:F9,MATCH(B34,B6:B9,0),0) INDEX(C6:F9,0,MATCH(C33,C5:F5,0))
5. Non-contiguous Ranges
The less commonly used area_num argument allows you to reference multiple non-contiguous ranges. The references to the ranges are wrapped in parentheses and separated by a comma. I’ve named the ranges Product1 (C6:F9) and Product2 (H6:K9):
The areas are numbered in the order you enter them, so in this example, product 1 is the first area and product 2 is the second area.
Note: Cell B13 contains the area_num. I’ve formatted it with a custom number format so that the text, ‘Product’, prefixes the number. You can see the format in the dialog box image below:
Notes:
- The area ranges must be on the same sheet, otherwise INDEX will return the #VALUE! error.
- The area ranges don’t need to be the same size, however it they aren’t it will be tricky to choose the correct row or column as it’s expected the structure of the tables is the same.
Anil N
This is much helpful, thanks!
One question- Is there any way to fetch 10 or 20 rows in sequence in a column using the INDEX function? I am just trying to have a workaround for the QUERY functions of Google spreadsheets. Any insights on this would be helpful.
Thanks,
Anil N
Mynda Treacy
Hi Anil,
Probably, but it’s difficult to help without an example. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Mynda
Yog Pat
Hi Friends,
How to retrieve Top 3 Max value if it’s that value repeating 2 times or more in excel column.
Function must return max value only.
Eg.
3
8
3
5
4
7
7
5
7
150
Ans.
Top 1 7
Top 2 5
Top 3 2
Ans.
Thanks.
Yog Pat
Mynda Treacy
Hi Yog,
Not sure where you get 2 for the top 3, but if you have Microsoft 365 you can use this formula:
=LARGE(UNIQUE(FILTER(A2:A11,COUNTIF(A2:A11,A2:A11)>1)),{1;2;3})
Where your list of numbers is in cells A2:A11.
Mynda
Yog Pat
Thanks for reply
Ans for Top 3 is value “3”
I don’t have Microsoft 365.
Any way in older version . Please help.
Mynda Treacy
Hi Yog,
You can try doing this in 3 formulas:
First formula; select cells B2:B11 i.e. the same number of cells that you have values for in column A. Then enter this formula:
=IF(COUNTIF($A$2:$A$11,$A$2:$A$11)>1,$A$2:$A$11,0) and press CTRL+SHIFT+ENTER to enter it.
Second formula: select cell C2 and enter this formula:
=IF(COUNTIF($E$2:E2,E2)<2,E2,0) and press ENTER to enter it then copy down to row 11.
Third formula: select cell D2 and enter this formula:
=LARGE($F$2:$F$11,ROW(A1)) and press ENTER to enter it then copy down to row 4.
If you're still stuck, please post your question on our Excel forum where you can also upload a sample file and we can help you further.please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Mynda
Cyprian Ebong
Very good lesson
Mynda Treacy
Glad it was helpful!
GOPAL SHARMA
I HAVE MULTIPLE VALUES IN A CELL
9, 11, 13, 15, 17 ETC(COMMA MEANS NEXT CELL IN A ROW)
12, 14, 16, 18, 20
21, 23, 25, 27, 29
NOW I NEED TO MATCH ANY VALUE(7,11,14,27) IN A SINGLE ROW
IF MATCH I NEED THAT CELL VALUE
“7” DOES NOT EXISTS BUT “11” EXISTS SO I NEED RESULT AS 11 IN 1ST ROW
14 EXISTS SO I NEED RESULT AS 14 IN NEXT ROW
27 EXISTS SO I NEED RESULT AS 27 IN NEXT ROW
WHEN I FINISH FORMULA, CELL MUST SHOW RESULT AS 11 IN 1ST ROW
OR, ELSE FUNCTION NOT WORKING, I HAVE TRIED SEVERAL TIMES
Mynda Treacy
Hi Gopal, please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Abbott Katz
I enjoyed your discussion of INDEX. With respect to the space operator, if you name the ranges via Create from Selection you can write this formula:
=range1 range2
and return the value at the intersection
With Excel 365, you can identify unique pairs of values in non-contiguous fields (say fields 2 and 4) this way:
=UNIQUE(INDEX(dataset,SEQUENCE(ROWS(dataset),,,),{2,4}))
Thanks,
Abbott Katz
Mynda Treacy
Yes, naming the ranges will also work with the space operator, as I covered here many years ago now!
You can also use CHOOSE to trick INDEX into referencing areas on different sheets, but I didn’t want to go down that rabbit hole! Thanks for sharing though 🙂
jim
Hi Mynda,
How does having to work from home for a change suit you? 😉
I’m sure I used to be able to con INDEX into using multiple ranges on different sheets by using names ranges but I can’t reproduce it – perhaps a lesson in using the tools given in the way they were intended
Leaving a parameter blank to select a whole column/row is so useful with dynamic formulae but I’ve found that using the colon operator can make a formula absurdly long and more awkward to parse
Although this was only an illustration, the example for a space operator uses two range-returning INDEX functions to effectively mimic one straightforward INDEX Function
another great post; I always look forward to them
Mynda Treacy
Cheers, Jim! You can trick INDEX into referencing ranges on different sheets with CHOOSE, but I decided not to go down that rabbit hole 🙂 I agree, I could have thought of a better space operator example!