Return the value at a given row and column position within a range.
Syntax
INDEX(array, row_num, [column_num])Arguments
arrayrequired
The range or array to return a value from.
row_numrequired
The row position within the array.
column_numoptional
The column position within the array.
INDEX returns the value at a numeric position in a range. On its own it's simple, but paired with MATCH it becomes the most flexible lookup in Excel — able to look up in any direction and survive column inserts. INDEX/MATCH is the classic alternative to VLOOKUP in versions without XLOOKUP.
=INDEX(C2:C100, 5)→$420Returns the 5th value in the price column.
=INDEX(A2:D100, MATCH("A102", A2:A100, 0), 3)→$1,250INDEX/MATCH: finds the row for A102, returns column 3.
Choose the return range
Point INDEX at the column or table that holds your answer.
Find the row with MATCH
Nest MATCH to turn a lookup key into the row number INDEX needs.
Add a column number if needed
For two-dimensional tables, supply both row and column positions.
INDEX/MATCH can return values to the left of the key, doesn't break when columns are inserted, and is faster on large sheets.