The INDEX function is to return the value of a data array, which is selected by the row and column number.
Formula:
= INDEX(array, row_num, [column_num])
Explanation:
- Array is required, a range of cells or an array constant.
- Row_num is required, the row in array from which to return a value. Column_num is required when Row_num is omitted.
- Column_num is optional, the column in array from which to return a value. Row_num is required when Column_num is omitted.
Cautions:
- When both the Row_num and Column_num arguments are used, INDEX returns the value in the cell at the intersection of Row_num and Column_num.
- When the Row_num or Column_num argument is set to 0 (zero), INDEX returns the values for the entire column or row. To enter an array formula, press CTRL+SHIFT+ENTER.
Example 1: Look for a value in a cell
= INDEX(A2:D6,4,3)
In the data range A2:D6, look for the value in the 4th row and 3rd column. The result returns the value of cell C5, which is "BROWN".
Example 2: Look for the values for the row
{=INDEX(A2:D6,3,0)}
Step 1: Select the same number of cells as the number of cells in the data range.
Step 2: Type the formula "=INDEX(A2:D6,3,0)";
Step 3: Press Ctrl+Shift+Enter from the keyboard;
Step 4: The result will return the values of the whole row: "3 Liam WILLIAMS $61,877".
Example 3: Look for the values in multiple data range
=INDEX((A2:D6,A8:D12),3,2,1)
Step 1: Type the formula =INDEX((A2:D6,A8:D12),3,2,1) in a blank cell;
Step 2: The result will return the data in the intersection of 3rd row and 2nd column is data section 1.