Third item of a list
| A | |
|---|---|
| 1 | City |
| 2 | Boston |
| 3 | Denver |
| 4 | Austin |
| 5 | Seattle |
Formula: C1: =INDEX(A2:A5,3)
Result: Austin
Returns the 3rd cell of A2:A5, which is Austin.
Lookup & reference
INDEX
Value at the intersection of a row and a column.
Syntax
INDEX(array, row_num, [column_num])Example: =INDEX(C2:C100,MATCH(A2,B2:B100,0))
Available in: All versions
INDEX returns the value at a given row (and column) position inside a range. On its own it picks the nth item of a list; combined with MATCH it becomes a flexible lookup that can look in any direction and survives inserted columns.
When to use it
- You know the position (row 3, column 2) and want the value there.
- You need a lookup that returns a column to the left of the key.
- You want a two-way lookup, finding a value at the crossing of a row label and a column label.
- You need compatibility with Excel versions that lack XLOOKUP.
Arguments explained
- array. The range or array that contains the value you want. It can be a single column, a single row or a block of cells.
- row_num. The row inside array to return, counted from 1. Use 0 to return the entire column when array has several columns.
- column_num. Optional for one-dimensional ranges and required for blocks. It is the column inside array, counted from 1. Use 0 to return the whole row.
- area_num. Optional and rarely needed. When the first argument is several ranges in parentheses, it picks which of them to use.
Worked examples
Row and column position
| A | B | C | |
|---|---|---|---|
| 1 | Region | Q1 | Q2 |
| 2 | East | 120 | 135 |
| 3 | West | 98 | 110 |
| 4 | North | 75 | 92 |
Formula: E1: =INDEX(A1:C4,3,3)
Result: 110
Row 3, column 3 of A1:C4 is the West Q2 figure.
INDEX + MATCH two-way lookup
| A | B | C | |
|---|---|---|---|
| 1 | Region | Q1 | Q2 |
| 2 | East | 120 | 135 |
| 3 | West | 98 | 110 |
| 4 | North | 75 | 92 |
Formula: E1: =INDEX(B2:C4,MATCH("North",A2:A4,0),MATCH("Q1",B1:C1,0))
Result: 75
The first MATCH finds the row of North (3), the second finds the column of Q1 (1), and INDEX returns the crossing.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in F2: =INDEX(B2:B6,MATCH(104,A2:A6,0))
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Item | Price | Stock | Result | |
| 2 | 101 | Pen | 2.5 | 120 | Mug | |
| 3 | 102 | Notebook | 4 | 80 | ||
| 4 | 103 | Lamp | 25 | 15 | ||
| 5 | 104 | Mug | 7.5 | 40 | ||
| 6 | 105 | Backpack | 39 | 12 |
Result in F2: Mug
Common errors and how to fix them
- #REF!. The row or column number is larger than the range. INDEX counts from the first cell of the range you pass, not from A1.
- #VALUE!. The row or column number is text or an error. If it comes from MATCH, check that MATCH found something.
- Returns a whole row or column. A row or column number of 0 returns the entire row or column. That is intended, but in older Excel it must be array-entered.
- Wrong item after sorting. A hard-coded position no longer points at the same record. Derive the position with MATCH.
Related functions
Frequently asked questions
- What does INDEX do in Excel?
- It returns the value (or reference) at a specified row and column inside a range you give it.
- Why use INDEX MATCH instead of VLOOKUP?
- The lookup column can be anywhere, inserting columns does not break the formula, and it avoids the fragile column-number argument.
- Can INDEX MATCH return multiple results?
- A single formula returns one value. For several matches use FILTER in Excel 2021 and Microsoft 365.
- What is the difference between INDEX and MATCH?
- MATCH finds a position, INDEX turns a position into a value. They are two halves of one lookup.