The Excel INDEX function returns the value at a given row and column of a range. On its own it answers "what's in row 3, column 2?" — but its real power appears when you pair it with MATCH to build lookups that are more flexible than VLOOKUP and work in every version of Excel.
=INDEX(C2:C10, MATCH("Apr", A2:A10, 0)).
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The range to pull a value from. |
row_num | Required | Which row of the array (1 = first row of the array, not of the worksheet). Use 0 to return the entire column. |
column_num | Optional | Which column of the array. Required when the array has multiple columns. Use 0 to return the entire row. |
Positions are relative to the array, not the sheet. In =INDEX(C5:E20, 1, 1), row 1 / column 1 means cell C5. INDEX also has a rarely-used "reference form" with multiple areas — you can ignore it for everyday work.
Get a value by position
The grid below holds monthly sales by region. The data area is B2:E7. To get the value in its 3rd row, 2nd column (Mar / South):
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | North | South | East | West |
| 2 | Jan | $8,200 | $7,100 | $9,400 | $6,800 |
| 3 | Feb | $7,900 | $7,600 | $8,800 | $7,200 |
| 4 | Mar | $8,600 | $8,050 | $9,100 | $7,500 |
| 5 | Apr | $9,100 | $8,400 | $9,700 | $8,100 |
| 6 | May | $9,800 | $8,900 | $10,300 | $8,600 |
| 7 | Jun | $10,400 | $9,300 | $10,900 | $9,000 |
For a one-column (or one-row) range, you only need one position number:
Try it: interactive INDEX demo
Pick a row and column number and watch INDEX walk the grid above.
INDEX + MATCH: the classic combo
Hard-coded positions are fragile. MATCH finds positions by value, and INDEX turns them into results. Find April's sales for the East region without typing any numbers:
Why this combo earned its reputation:
| Capability | INDEX + MATCH | VLOOKUP |
|---|---|---|
| Look left of the lookup column | Yes | No |
| Survives inserted/deleted columns | Yes | No |
| Two-way (row and column) lookup | Yes | No |
| Works in every Excel version | Yes | Yes |
In Excel 2021+/365, XLOOKUP does all of this with one function — but INDEX/MATCH remains the portable choice for files that must open everywhere.
Whole rows, columns, and dynamic ranges
Set row_num to 0 and INDEX returns the entire column — useful inside other functions:
INDEX actually returns a reference, not just a value, so it can serve as the endpoint of a range. This builds a range from A2 down to the nth row — a non-volatile alternative to OFFSET:
Errors & common pitfalls
#REF! — position out of range. Asking for row 8 of a 6-row array. Check that row_num and column_num fit inside the array's actual size.
#VALUE! — missing column_num. If the array has multiple columns, INDEX needs both a row and a column number.
Pitfall: counting from the sheet instead of the array. Positions are relative to the array's top-left cell. If your array starts at C5, then row 1 means worksheet row 5.
Pitfall: swapping row and column. INDEX takes row first, then column — the opposite "feel" of cell addresses like C5. When a result looks transposed, this is why.
Practice workbook
Frequently asked questions
What is the difference between INDEX and VLOOKUP?
Should I use INDEX/MATCH or XLOOKUP?
What does row_num = 0 do in INDEX?
Can INDEX return a range, not just a value?
Why is INDEX better than OFFSET?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class