Returns the value at a row and column position in a range. Paired with MATCH it looks up in any direction; with a 0 row or column it returns a whole column or row.

Syntax

=INDEX(array, row_num, [column_num])

Example

=INDEX(C2:C50,MATCH(F1,A2:A50,0))

• Position in a column

  =INDEX(B2:B6,3) → 15

  The third price.

• Row and column

  =INDEX(A2:C6,2,3) → East

  Row 2, column 3 of the table.

• A whole column

  =INDEX(A2:C6,0,2) → {10;25;15;40;55}

  A zero row returns the whole second column, which spills.

• Row or column 0 returns the whole column or row, which is how INDEX feeds other functions.
• INDEX/MATCH looks left, which VLOOKUP cannot: =INDEX(A2:A6,MATCH(15,B2:B6,0)).
• INDEX returns a reference, so =SUM(B2:INDEX(B2:B6,3)) sums a growing range without OFFSET's volatility.
• Two MATCHes give a two-way lookup.

• #REF! when the row or column is outside the range.
• A single-column range with two indexes given still needs the column as 1.