The Excel INDEX function can be used to return a value or item at a certain position within a range. The Excel INDEX function can as well be used to get individual values or that of the entire columns and rows within a table. Most times, INDEX is used hand-in-hand with the MATCH function, and in this case, the MATCH function locates and provides a location to the INDEX function.
Used in getting a value within a list or table based on its location
The exact value matching the given location along the row and down the column.
- Array: This is a range of cells within the table or an array constant.
- Row Number: This is the row number the exact value sort for appears in the array.
- Column Number: This is the column number the exact value sort for appears in the array (optional).
- Area Number: This is the reference to the range that will be used (optional)
The example below uses the formula:
And this will return the value in cell D13.
Figure 1: Example of how to use the Excel INDEX Function to get the population of Sweden using the array format
Usage Notes for the Excel INDEX function
To get a value from a list or table based on its location, the Excel INDEX function can be used.
Array and Reference are the two forms of the Excel INDEX function.
Array Form of Excel INDEX Function
Here, the first factor is an array and this is given as a range of cells or an array constant. The formula for the array form of INDEX is given below:
In the reference form of INDEX, reference is the first factor and this is provided as a reference to one cell range or more cells or arrays within the table.
Then the formula for the reference form of INDEX function is:
Here, the reference form of Excel INDEX function displays the reference of the cell at the point of intersection between the row number and column number.
Area number will indicate which range to be used when the reference is presented as multiple arrays within a table.
Area sum is usually provided as a figure. For instance, if we have a formula as =INDEX((B1:D5,B7:E10),2,2,2). The area number in this formula will be 2 which is a representation of the range between B7:E10.
- The Excel INDEX function will display the value at the point of intersection of both the col_num and row_num and when the row_num and col_num are provided
- The Excel INDEX function will display a range of values for the entire row.
- The Excel INDEX function will display a range of values for the complete column when the column number is zero.