The INDEX function in Excel is a powerful tool used to return the value of an element in a table or array, selected by the row and column number indexes. It is particularly useful for looking up values in a table based on row and column coordinates.
Syntax
The syntax for the INDEX function is as follows:
| Function | Description |
|---|---|
=INDEX(array, row_num, [column_num]) | Returns the value of an element in a table or array, selected by the row and column number indexes. |
array: A range of cells or an array constant.
row_num: The row number in the array from which to return a value.
column_num: (Optional) The column number in the array from which to return a value. If omitted, the default is the first column.
Example
Let's consider the following table of student scores:
To find the Science score of Bob using the INDEX function:
=INDEX(B2:D5, 2, 2)This formula will return 83, as it selects the value from the 2nd row and 2nd column within the specified range B2.
Exercise
Practice Exercise 1
Task: Use the INDEX function to find the English score of Charlie from the following table:
This formula will return 94, as it selects the value from the 3rd row and 3rd column within the specified range B2.
Practice Exercise 2
Task: Use the INDEX function to find the Math score of Alice from the following table:
Solutions
A Solution to Exercise 1
To find the English score of Charlie:
=INDEX(B2:D5, 3, 3)This formula will return 94, as it selects the value from the 3rd row and 3rd column within the specified range B2.
A Solution to Exercise 2
To find the Math score of Alice:
=INDEX(B2:D5, 1, 1)This formula will return 85, as it selects the value from the 1st row and 1st column within the specified range B2.
By using these examples and exercises, you can get a better understanding of how the INDEX function works in Excel and how it can be applied to retrieve specific data from a table or array.