Excel INDEX Function
INDEX Function
The INDEX function is a premade function in Excel, which returns the value at a given position in a range.
You give it a range and a row number, and if needed a column number. INDEX returns the value in that cell.
It is typed =INDEX and has the following parts:
=INDEX(array, row_num, [column_num])
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Array: The range to return a value from.
Row_num: The row in the range, counted from the top of the range.
Column_num: Optional. The column in the range, counted from the left of the range. You can leave it out when the range is a single column.
Note: Positions count from the top-left cell of the range you give, not from the top of the sheet. Row 1 of B2:B21 is sheet row 2.
INDEX also has a second form with a fourth part, area_num, for picking from several ranges. This page covers the common form shown above.
How to use the INDEX function:
- Select a cell (
H4) - Type
=INDEX - Double click the INDEX command
- Mark the range (
B2:B21) - Type (
,) - Select the cell where the row number will be entered (
H3) - Hit enter
- Enter a number in
H3
Let's have a look at an example!
This is the same Pokemon table as on the VLOOKUP page. Copy it and paste it into cell A1 to follow along:
Example data
A B C D E 1 ID# Name Type 1 Type 2 Total 2 1 Bulbasaur Grass Poison 318 3 2 Ivysaur Grass Poison 405 4 3 Venusaur Grass Poison 525 5 4 Charmander Fire 309 6 5 Charmeleon Fire 405 7 6 Charizard Fire Flying 534 8 7 Squirtle Water 314 9 8 Wartortle Water 405 10 9 Blastoise Water 530 11 10 Caterpie Bug 195 12 11 Metapod Bug 205 13 12 Butterfree Bug Flying 395 14 13 Weedle Bug Poison 195 15 14 Kakuna Bug Poison 205 16 15 Beedrill Bug Poison 395 17 16 Pidgey Normal Flying 251 18 17 Pidgeotto Normal Flying 349 19 18 Pidgeot Normal Flying 479 20 19 Rattata Normal 253 21 20 Raticate Normal 413
Type the labels Row in G3 and Name in G4.
Use the INDEX function to return the 7th name in the list. Type 7 in H3:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | ID# | Name | Type 1 | Type 2 | Total | |||
| 2 | 1 | Bulbasaur | Grass | Poison | 318 | |||
| 3 | 2 | Ivysaur | Grass | Poison | 405 | Row | 7 | |
| 4 | 3 | Venusaur | Grass | Poison | 525 | Name | Squirtle | |
| 5 | 4 | Charmander | Fire | 309 | ||||
| 6 | 5 | Charmeleon | Fire | 405 | ||||
| 7 | 6 | Charizard | Fire | Flying | 534 | |||
| 8 | 7 | Squirtle | Water | 314 | ||||
| 9 | 8 | Wartortle | Water | 405 | ||||
| 10 | 9 | Blastoise | Water | 530 | ||||
| 11 | 10 | Caterpie | Bug | 195 | ||||
| 12 | 11 | Metapod | Bug | 205 | ||||
| 13 | 12 | Butterfree | Bug | Flying | 395 | |||
| 14 | 13 | Weedle | Bug | Poison | 195 | |||
| 15 | 14 | Kakuna | Bug | Poison | 205 | |||
| 16 | 15 | Beedrill | Bug | Poison | 395 | |||
| 17 | 16 | Pidgey | Normal | Flying | 251 | |||
| 18 | 17 | Pidgeotto | Normal | Flying | 349 | |||
| 19 | 18 | Pidgeot | Normal | Flying | 479 | |||
| 20 | 19 | Rattata | Normal | 253 | ||||
| 21 | 20 | Raticate | Normal | 413 |
=INDEX(B2:B21, H3)
INDEX counts 7 rows down from the top of B2:B21. The 7th cell is B8, which holds Squirtle.
You can also type the number straight into the formula. =INDEX(B2:B21, 7) returns Squirtle as well.
Row and Column
When the range has several columns, add a column number. INDEX returns the value where the row and the column meet.
Change the label in G4 to Column, and type Value in G5. Type 6 in H3, 5 in H4, and the formula in H5:
| G | H | |
|---|---|---|
| 3 | Row | 6 |
| 4 | Column | 5 |
| 5 | Value | 534 |
=INDEX(A2:E21, H3, H4)
Row 6 of A2:E21 is the row for Charizard (sheet row 7). Column 5 is Total (column E). So INDEX returns 534 from E7.
With the numbers typed in, the formula is =INDEX(A2:E21, 6, 5). It returns 534 as well.
Positions Count From the Range
INDEX counts from the first cell of the range you give it. That means the same cell can have different positions in different ranges:
| Formula | Result | Cell |
|---|---|---|
| =INDEX(B2:B21, 7) | Squirtle | B8 |
| =INDEX(B8:B21, 1) | Squirtle | B8 |
| =INDEX(B1:B21, 7) | Charizard | B7 |
| =INDEX(A2:E21, 6, 5) | 534 | E7 |
| =INDEX(C2:E21, 6, 3) | 534 | E7 |
Look at B1:B21. It starts at the header row, so row 7 of this range is sheet row 7, which holds Charizard, not Squirtle.
Tip: Check where your range starts. Adding or leaving out the header row changes every position by one.
Position Outside the Range
If the row number or column number is larger than the range, INDEX returns the #REF! error.
B2:B21 has 20 rows, so this formula returns #REF!:
=INDEX(B2:B21, 21)
INDEX With MATCH
INDEX needs a position. Often you do not know the position, only the value, like the name of a Pokemon.
The MATCH function finds the position for you:
=INDEX(E2:E21, MATCH("Squirtle", B2:B21, 0))
MATCH returns 7, the position of Squirtle in B2:B21. INDEX then returns the 7th value in E2:E21, which is 314.
Learn more in the INDEX and MATCH chapter.
