The INDEX function in Excel
INDEX returns the value at a given position inside a range: "row 3, column 2 of this table". On its own it's handy for picking the nth item from a list. Paired with MATCH it becomes the classic flexible lookup that interviewers still love to test.
Syntax
=INDEX(array, row_num, [column_num])
| array | The range to pick from, like B2:D5. |
|---|---|
| row_num | Which row of the range to return, counting from 1 at the top of the range (not the sheet). |
| column_num | Optional. Which column of the range to return. Leave it out when the range is a single column. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
The sales leaderboard is sorted by rank. In G2, use INDEX to return the rep's name for the place number in F2.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Revenue | Rep | ||||||
| 2 | ||||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 | ||||||||
| 9 | ||||||||
| 10 |
Tip: Give INDEX the list of rep names and use F2 as the row number.
Examples
=INDEX(B2:D5,3,2)→ 10.4Third row, second column of the rate grid: the Medium parcel rate for Zone 3.
=INDEX(A2:A5,4)→ "Zone 4"A one-column range only needs a row number. This returns the fourth zone name.
=INDEX(B2:D5,MATCH("Zone 2",A2:A5,0),MATCH("Large",B1:D1,0))→ 13.5A two-way lookup: one MATCH finds the row for Zone 2, the other finds the column for Large, and INDEX returns the rate where they meet.
Example data
| 1 | Zone | Small | Medium | Large |
|---|---|---|---|---|
| 2 | Zone 1 | 4.5 | 7.25 | 11 |
| 3 | Zone 2 | 5.75 | 8.9 | 13.5 |
| 4 | Zone 3 | 6.95 | 10.4 | 15.8 |
| 5 | Zone 4 | 8.2 | 12.1 | 18.6 |
Common mistakes
Counting sheet rows instead of range rows
In
INDEX(B2:B10,3), position 3 is cell B4, not row 3 of the sheet. Count from the first cell of your range.#REF! from a position that's too big
Asking for row 6 of a five-row range returns #REF!. Check the size of the range, or let MATCH work out the position for you.
Row and column swapped
The order is always row first, then column.
INDEX(B2:D5,2,3)is row 2, column 3, not the other way round.Hardcoding positions
Typed positions break when data is sorted or a row is added. Use
MATCH(value, range, 0)to calculate the position instead.
FAQ
What does INDEX do in Excel?+
It returns whatever is at a row (and optionally column) position inside a range. Think of it as giving Excel map coordinates for a cell.
Why use INDEX MATCH instead of VLOOKUP?+
INDEX/MATCH can look left, doesn't break when columns are inserted, and works in every Excel version. It's also a common interview question to check you understand lookups beyond VLOOKUP.
Can INDEX return a whole row or column?+
Yes. Use 0 for the row number to get an entire column, like INDEX(B2:D5,0,2), or 0 for the column to get a whole row. In modern Excel the result spills into neighboring cells.
Lesson
Master INDEX step by step: INDEX and MATCH
Combine INDEX and MATCH to look up in any direction, including returning a column to the left of the one you search.