=INDEX(A2:C6,3,2) returns the value in the third row and second column of the range A2:C6. Positions count from the range's top left cell, so row 3 of A2:C6 is sheet row 4. Change the 3 or the 2 and watch the result move.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Row | Column | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | Vegetable | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
E2 holds the row number and F2 the column number. Row 3 is Carrot and column 2 is Category, so G2 shows Vegetable. Set F2 to 1 for the product name, or E2 to 6 to see #REF!: A2:C6 has only five rows.
INDEX syntax
=INDEX(array, row_num, [column_num])
array: the range (or array) to read from.row_num: which row of it, starting at 1. Use 0 for all rows.column_num: which column, starting at 1. Optional when the range is a single column or a single row; use 0 for all columns.
On a single column, one number is enough: =INDEX(A2:A6,4) is the fourth item, Bread. A second form, =INDEX((A2:C3,A5:C6),1,1,2), picks from one of several ranges; it is rarely needed.
Get the nth item, or the last one
INDEX with a single column answers "what is item number n". Combined with COUNTA, which counts the filled cells, it returns the last item of a list that grows.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Item number | 2 | |
| 2 | Apple | Nth item | Pear | |
| 3 | Pear | Last item | Milk | |
| 4 | Carrot | |||
| 5 | Bread | |||
| 6 | Milk |
D1 says 2, so D2 returns Pear. COUNTA counts 5 products, so D3 returns the fifth, Milk. Delete Milk and D3 returns Bread. In a real file, point both at a longer range such as A2:A1000 so new rows are included; COUNTA only works this way when the column has no empty cells in the middle.
Return a whole row or column with 0
A 0 as the row number means "every row", so INDEX(B2:D5,0,2) is the whole second column. Inside SUM, AVERAGE or MAX, that totals a column chosen by number.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar | Month number | Total | |
| 2 | North | 4,200 | 3,900 | 4,800 | 2 | 15,200 | |
| 3 | South | 3,100 | 3,600 | 3,300 | |||
| 4 | East | 5,200 | 4,700 | 5,600 | |||
| 5 | West | 2,800 | 3,000 | 3,400 |
Month 2 is Feb, and G2 adds C2:C5: 15,200. Change F2 to 3 for March. A whole row works the same way: =SUM(INDEX(B2:D5,3,0)) totals East. In Excel 2021 and Microsoft 365, =INDEX(B2:D5,0,2) on its own spills the four values down the sheet. To choose the column by its header instead of a number, replace F2 with a MATCH, which is the INDEX and MATCH pattern.
INDEX on an array or a spilled result
INDEX also reads arrays that a formula returns, not only ranges on the sheet. That picks one item out of a sorted, filtered or unique list without writing the list out first.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Most expensive | Bread | |
| 2 | Apple | Fruit | $1.20 | Second | Pear | |
| 3 | Pear | Fruit | $1.50 | Cheapest | Carrot | |
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
SORTBY returns the five products ordered by price, and INDEX takes item 1 (Bread), item 2 (Pear) or, from the ascending sort, item 1 (Carrot). Change Milk's price to 3 and it becomes the most expensive. If a spilled list already sits on the sheet, say in H2, Excel 2021 and Microsoft 365 let you write =INDEX(H2#,2) for its second item.
Practice: total a chosen month
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar | Month number | Total | |
| 2 | North | 4,200 | 3,900 | 4,800 | 1 | ||
| 3 | South | 3,100 | 3,600 | 3,300 | |||
| 4 | East | 5,200 | 4,700 | 5,600 | |||
| 5 | West | 2,800 | 3,000 | 3,400 |
Your turn: In G2, return the total of the month whose number is in F2 (1 = Jan, 2 = Feb, 3 = Mar), using INDEX.
Frequently Asked Questions
What does the INDEX function do in Excel?
It returns the value at a given position in a range: =INDEX(A2:C6,3,2) returns the value in the third row and second column of A2:C6. Positions count from the top left cell of the range, not from row 1 of the sheet.
How do I get the last value in a column with INDEX?
Use the count of filled cells as the row number: =INDEX(B2:B100,COUNTA(B2:B100)) returns the last value of a column with no gaps. With gaps, =LOOKUP(2,1/(B2:B100<>""),B2:B100) returns the last non-empty value.
Why does INDEX return #REF!?
The row or column number is larger than the range. =INDEX(A2:A6,7) asks for the seventh item of a five-cell range and returns #REF!.
How do I return a whole column with INDEX?
Use 0 as the row number: =INDEX(B2:D5,0,2) returns all of the second column. Wrap it in a function to total it, as in =SUM(INDEX(B2:D5,0,2)), or let it spill in Excel 2021 and Microsoft 365.
