INDEX Function – Examples in Excel, VBA, & Google Sheets
Download the example workbook
This tutorial demonstrates how to use the INDEX Function in Excel and Google Sheets to return a value based on column and row references.
w
What is the INDEX function?
The INDEX Function returns a cell value from a list or table based on it’s column and row numbers.
Basic Example
We will start with a simple list of items (column A below). We want our formula to return the 3rd item from the list.
=INDEX(A2:A5, D1)
Within the INDEX Function, first we enter the array to search. Then we specify the row number of the item we want.
Table Example
Let’s build a multiplication table and demonstrate how to use the INDEX Function to return an item based on row / column index.
To get the result of 3 times 4 (and not just write the math equation), you could write a formula of
=INDEX(B2:E5, 3, 4)
In this scenario, we’ve told the formula to go to the 3rd row within the specified range, and the 4th column. This is going to give a reference to cell E4, and we thus get a value of 12.
Table Example – Text
We can do the same with text values.
=INDEX(B2:D5, 3, 2)
Here, our formula goes down to the 3rd row (for the 3rd quarter) and the 2nd column. That returns a reference to C4 and the result of “Aug”.
MATCH with INDEX
The INDEX Function is often used in conjunction with the MATCH Function to create a lookup formula.
Let’s look at a grade book example where our teacher wants to be able to give a subject and student name and find the corresponding grade.
Our formula is.
=INDEX(B2:E5, MATCH(H1, A2:A5, 0), MATCH(H2, B1:E1, 0))
We’ve used two different MATCH function to return the relative position of each of our search terms. The first one is looking for the value of H1, “Reading”, in the range of A2:A5. Reading is the 3rd item in the list. Our 2nd MATCH is similarly looking for the value of “Bob” within the range B1:E1. Bob is the 2nd item in this list. Thus, our formula will be evaluated like so:
=INDEX(B2:E5, MATCH(H1, A2:A5, 0), MATCH(H2, B1:E1, 0))
=INDEX(B2:E5, 3, 2)
=C4
=89
You can learn more about the INDEX / MATCH Formula by clicking the link.
INDEX in Google Sheets
The INDEX Function works exactly the same in Google Sheets as in Excel:
INDEX Examples in VBA
You can also use the INDEX function in VBA. Type:
application.worksheetfunction.index(array,row_num,column_num)
For the function arguments (array, etc.), you can either enter them directly into the function, or define variables to use instead.
Assuming we have the following data in our worksheet
There are two tables defined in this example, one table on the left which is named Table2 and on the right Table3
We can use the INDEX function as following
WorksheetFunction.Index(Range("B2:F6"), 3, 3)
This will return 18 (Cell D4), because it is the cell located at the third row and third column cell in our defined range B2:F6
WorksheetFunction.Index(Range("Table2"), 2, 4)
This will return 51 (Cell E3), because it is the cell located at the second row and fourth column in Table2
We can also define more that one ranges, as following.Notice that we are using one more parameter this time (the last “1”), which defines the range from which the value should be returned
WorksheetFunction.Index(Range("B2:F6,I4:M8"), 2, 5, 1)
This will return 36 (Cell F3), because it is the cell located at the second row and fifth column in our first defined range, which is Table2
WorksheetFunction.Index(Range("B2:F6,I2:M6"), 4, 3, 2)
This will return 115 (Cell K5), because it is the cell located at the fourth row and third column in our second defined range, which is I2:M6