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.
Excel shows Smoking
B
C
D
E
1
Item
Category
Qty
Unit Value
2
Boxer cartridge
Tools
12
$3.50
3
Briar pipe
Smoking
7
$4.25
4
Candle
Household
20
$2.00
5
Cigar
Smoking
5
$10.00
6
Clay pipe
Smoking
15
$1.75
7
Commonplace book
Papers
9
$6.00
Items
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.
| Formula | Excel shows | Case |
|---|---|---|
=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
B
C
D
E
1
Item
Category
Qty
Unit Value
2
Boxer cartridge
Tools
12
$3.50
3
Briar pipe
Smoking
7
$4.25
4
Candle
Household
20
$2.00
5
Cigar
Smoking
5
$10.00
6
Clay pipe
Smoking
15
$1.75
7
Commonplace book
Papers
9
$6.00
Items
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 .