INDEX formula in Excel returns the cell at the intersection of a particular row and column within a range.
The INDEX formula is typically used to locate a value in a given range by specifying its X-Y coordinates (i.e. the row and the column) within the range.
INDEX Formula Syntax
INDEX Formula has four parts:
INDEX (range_to_search_in, row_position, column_position, range_number)
range_to_search_in is the range in which you would like to locate the value. More than one range can be specified though a value will be returned from only one of the ranges specified. The last parameter (range_number) governs which range will be picked up for use in the formula.
row_position is relative row number within the range in which the value is to be located. Please bear in mind that this is not the absolute row number of the excel worksheet but the row number starting from the first cell of the range that we have provided as the argument.
column_position is relative column number within the range in which the value is to be located. Again, please bear in mind that this is not the absolute column number of the excel worksheet but the row number starting from the first cell of the range that we have provided as the argument.
range_number specifies the area to pick up when more than one range is specified in the argument.
Example of a INDEX Formula
Let’s us take a look at an example of the INDEX formula. Suppose we had a list of values in a table as shown in the example above. If we wanted to find out the value located at the intersection of a particular row and a column, we could write =INDEX(A2:C10,4,1) which would return the cell value located at the intersection of the fourth row and the first column (relative to the first cell) of the range specified. The output value in this case would be “Damon Hill” since it resides four rows down and in the first column counting from the first cell of the range (A2:C10).
INDEX Array formula
The above example highlights the use of the INDEX formula as a normal (array-less) formula. However one can use the INDEX formula to return a range of values as well. You can read more about array formulas here.
Suppose we had to return an entire row or column from a given range. In such a case we could set to corresponding column or row value to 0 and then enter the formula using CTRL + SHIFT + ENTER. In the above example, we want to return the entire third column from the specified range. So we write the array version of the INDEX formula as =INDEX(A2:C10,0,3). As you can see, when we need to return the entire column, we set the row parameter to 0. Similarly if we need to return the entire row, we would set the column parameter to 0.
Points to note about the INDEX Formula
One important aspect to note about the INDEX formula is the parameters mentioned above (i.e. row_position and column_position) are optional and if only one of them is specified, the other will automatically be “guessed” by the application based on the orientation of the range. For example, if in the above example you were to write =INDEX(A2:A10,3), the return value would be Terri Spencer. However if you were to write =INDEX(A2:C2,3), the return value would be 77. While in the former, a value of 3 would be used to signify the row, in the later case, the same specifies the column. Both of the above examples work because we use them with one-dimensional input range. If we were to specify a 2 dimensional range say, =INDEX(A2:C10,3) the formula would return an error value.
INDEX Formula with multiple range
You can specify more than one range in which you would like to locate a value. If more than one range is specified in an INDEX formula, the range to pick up needs to be specified as the fourth parameter. The multiple ranges are enclosed in an extra bracket and separated by commas. So if you were to write something like =INDEX((A2:C4,A8:C10),3,3,2), the return value would be 420. Why? Because the fourth parameter specifies that the second range should be used as the range in which the value is to be located. The value at the 3rd row and the 3rd column, relative to the first cell (of the second range), would be returned. If the fourth parameter is omitted, the first range is picked up default.
How to enter the INDEX formula in an Excel Sheet
1. Select the cell in which you want to place the formula
2. Type the formula as =INDEX(
3. Then using the mouse up-down and left-right keys, move to the first cell in range in which you would like to locate this value.
4. Press the comma key (,)
5. Type in the row number in which the value is to be located.
6. Press the comma key (,)
7. Then enter the column number in which the value is to be located.
8. Close the formula by entering the closing bracket ).
Check out the clip above for knowing if the values you’ve entered are in the same order. In the end your formula should look something like this =INDEX(A2:C2,3)
Possible Errors with the INDEX Formula
The INDEX formula can result in the following error values:
INDEX Formula #REF! Error
If the row or the column parameters provided to an INDEX formula try to locate a value beyond the number of rows and columns available in the specified range, a #REF! error value is returned. Similarly if multiple range are specified, and if you specify a range index beyond the available set as the fourth parameter, #REF! would result.
INDEX Formula #NAME? Error
If the parameters have been specified incorrectly (entering string instead of a number), the INDEX function can result in the #NAME? error. For example, say you entered “first” as the row number instead of 1 and entered =INDEX(A3:B6,first,3), that would result in the #NAME? error.
You can download an example of INDEX formula here or click on the button below: