Buzz
The INDEX function in array form returns the value of a specific part of a table or selected array based on the index numbers of the row and column. We use the array form when the first argument of the INDEX function is an array constant. The reference form of the INDEX function returns the reference of the cell located at the intersection of a specific row and column.
Example of the INDEX Function
GUIDE TO USING INDEX FUNCTION IN EXCEL - ILLUSTRATIVE EXAMPLES
1. Array Form of the INDEX Function
Syntax: (Array, Row_num, [Column_num])
Where:
- Array: Range of cells or a constant array, mandatory
- Row_num: Select the row in the array from which to retrieve a value
- Column_num: Select the column in the array from which to retrieve a value.
At least one of Row_num and Column_num must be specified.
Example 1: For a list of students, find the name of the student in row 2, column 2.
Formula in cell C9:=INDEX(B4:C7,2,2)
2. INDEX Function in Reference Form
Syntax: INDEX(Reference, Row_num, [Column_num], [Area_num])
Where:
- Reference: Reference range, mandatory.
- Row_num: Row index from which to return a reference, mandatory.
- Column_num: Column index from which to return a reference, optional.
- Area_num: Number of the cell area to return a value within the reference. If Area_num is omitted, INDEX uses area 1, optional.
Continuing Example 1:
Formula in cell C9 =INDEX(B4:C7,2,1,1)
With the Index function, you can reference any cell in an Excel spreadsheet. Therefore, this function is often used in conjunction with other Excel functions for more effective use. In fact, the Round function in Excel is another commonly used function; using the Round function helps round values in Excel.
Have you discovered how to combine the Index and Match functions in Excel? If you encounter a problem that requires the collaboration of these two functions, check out how to combine Index and Match in Excel.
