INDEX
Returns a value from a range by row and column number: the third row of a list, the cell where a row and a column of a table cross.
INDEX(array, row_num, [column_num])- [ ]optional part
- array
- The range to take the value from, for example $B$2:$B$7.
- row_num
- Which row of the range you need. The first row is 1.
- column_num
- Which column of the range you need. Leave it out for a single-column range.Optional
Return value
The value of the range cell with the given row and column numbers. If a number is outside the range, you get #REF!.
Examples
Revenue for a month by its number
fx
A
B
C
D
E
F
1
2
3
4
5
6
7
8
9
Month
Revenue, $
Month number
Revenue, $
January
412000
3
February
387500
March
455300
April
431800
May
478900
June
502100
Sheet1
Details
INDEX takes a range and a row number inside it. The number is counted from the start of the range, not from the start of the sheet: in the range B2:B7 the first row is B2.
If the range has several columns, the third argument is the column number. Then the function returns the cell where that row and column cross.
On its own, INDEX is rarely needed. More often the row number comes from MATCH: together they look up a value by key, like VLOOKUP, but in any direction.
Common mistakes
- The row number is counted from the top of the sheet. If the range starts on row 2, the result shifts one row down: count from the start of the range.
- The number is larger than the number of rows in the range. The function returns #REF!.
- The range includes the header. Then the first row is the header, and every number is off by one.