Practice free

INDEX function

INDEX returns the value at a given row and column of a range. Paired with MATCH, it makes a flexible lookup that works in any direction.

Example

Read it as a sentence: in B1:E7, go to the 3rd row and 2nd column and give me what is there, which is Smoking.

Practice this in the app

Syntax

INDEX(array, row_num, [column_num], [area_num])

Arguments in [square brackets] are optional.

array (required)
The range or array to read from.
row_num (required)
Row position within the array; 0 returns the whole column.
column_num (optional)
Column position; 0 returns the whole row.
area_num (optional)
Which area, if the reference has several.

Returns: The value (or reference) at the given position.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=INDEX({1,2,3},2) 2 Horizontal array, one index
=INDEX({1,2;3,4},2,2) 4 Array
=INDEX(Items!B1:E7,3,2) Smoking Row and column
=INDEX(Items!B1:E7,0,3) Qty Whole column
=INDEX(Items!B1:E7,3,0) Briar pipe Whole row
=INDEX(Items!B2:B7,3) Candle 1D range, one index
=INDEX(Items!B1:E7,3) #REF! 2D range, row only
=INDEX(Items!B1:E7,9,1) #REF! Row past range

The Items sheet these formulas read

Watch out for

  • Positions are relative to the range, not the sheet.
  • row_num 0 returns a full column, which can spill in 365.
  • For a one-row or one-column range, a single number picks the item.

Where it works

Excel
INDEX is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .