How to Use INDEX Function

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.

Download: INDEX Function

Leave a Reply