Function guides · Lookup
INDEX function
Returns the value at a given row and column position in a range.
Syntax
=INDEX(array, row_num, [column_num])
| Argument | What it is |
|---|---|
array | The range to pick from. |
row_num | Which row of the range, counting from 1. |
column_num | Optional. Which column, counting from 1. |
What INDEX does
INDEX returns the value sitting at a position inside a range. You give it the range and a row number, and for a table also a column number.
On its own it is rarely useful, since you could simply click the cell. Its value appears when the position is calculated by another function. MATCH finds where a value is; INDEX then picks up the matching item from a different column. Together they form the lookup that predates VLOOKUP’s popularity and outlasts its limits.
INDEX examples
1. Pick by position
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rank | Rep | ||
| 2 | 1 | Dana | Mei | |
| 3 | 2 | Luis | ||
| 4 | 3 | Mei | ||
| 5 | 4 | Omar |
INDEX counts inside the range you give it, not on the worksheet. B2:B5 is a four-row range, so row 3 is the third cell of it, B4, which holds Mei.
2. Row and column in a table
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Q1 | Q2 | ||
| 2 | Dana | 410 | 380 | 610 | |
| 3 | Luis | 295 | 340 | ||
| 4 | Mei | 520 | 610 |
With a two-dimensional range, give both numbers. This reads the third row and third column of A2:C4, which is the second-quarter figure for Mei.
3. INDEX and MATCH together
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Units | Region | ||
| 2 | Dana | 410 | West | East | |
| 3 | Luis | 295 | East | ||
| 4 | Mei | 520 | North |
MATCH finds the position of "Luis" in the name column and hands the number to INDEX, which reads the same position in the region column. This is a lookup that works in either direction.
Unlike VLOOKUP, the column you return can be to the left of the one you search.
Common INDEX mistakes
- Counting from the worksheet instead of the range.
=INDEX(B2:B5,3)is the third cell of the range, B4, not row 3 of the sheet. Count from the first cell you gave. - Ranges that do not line up. In INDEX and MATCH the lookup column and the return column should start on the same row. If one starts at row 2 and the other at row 1, every answer is off by one.
- Forgetting MATCH's zero.
MATCH(x,range)without 0 does an approximate match and can return the wrong row. Use 0 for exact. - Asking for a position outside the range. Row 10 of a four-row range returns #REF!.
- Using INDEX on a whole row when you need a cell. A row_num of 0 returns the entire column, which spills in current Excel or errors in older versions.
INDEX on Excel job tests
INDEX with MATCH comes up when VLOOKUP cannot do the job: the key is not in the leftmost column, or the prompt says not to use VLOOKUP. A two-way lookup is another favorite, where one MATCH finds the row and another finds the column.
The shape is INDEX(table, MATCH(row key, row labels, 0), MATCH(column key, column headers, 0)). Row labels go first, column headers second, and both MATCH calls end in 0.
It works in every version of Excel, which makes it the safe choice when the test does not say which version it runs on.
Related functions
INDEX questions
- What does INDEX do in Excel?
- It returns the value at a given row and column position within a range.
- Why use INDEX and MATCH instead of VLOOKUP?
- INDEX and MATCH can return a column to the left of the key, do not break when columns are inserted, and give you separate control of row and column.
- How do I do a two-way lookup?
- Nest two MATCH calls inside INDEX, one for the row position and one for the column position.
- Does INDEX work with whole columns?
- Yes,
=INDEX(B:B,5)returns B5. The row number then matches the worksheet row.
Practice INDEX
Try it in a working grid: the free lesson INDEX and MATCH: look in any direction (module 16 of Analyst) has checked INDEX exercises.
Practice this function