INDEX Function
Returns the value at a given row and column position in a range.
Category
Lookup & Reference
Available in
All versions
Returns
A value or reference
Overview
INDEX is like map coordinates. Give it a range, a row number and (optionally) a column number, and it returns whatever sits at that spot.
On its own it's simple. Paired with MATCH, which works out the position for you, it becomes one of the most flexible lookups in Excel — and it works in every version.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array | Yes | The range to pick from. |
| row_num | Yes | Which row of the range. Use 0 to return the whole column. |
| column_num | Optional | Which column of the range. Use 0 to return the whole row. |
| area_num | Optional | When array is several ranges in brackets, which one to use. |
Examples
Example 1 — Picking by position
Return the item in the 3rd row, 2nd column of the product table.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID | Product | Category | Price |
| 2 | P-101 | Keyboard | Accessories | 45 |
| 3 | P-102 | Monitor | Displays | 210 |
| 4 | P-103 | Mouse | Accessories | 25 |
| 5 | P-104 | Laptop | Computers | 1,150 |
| 6 | P-105 | Webcam | Accessories | 60 |
Example 2 — INDEX + MATCH: the classic lookup
Find the price for a product name typed in F2. MATCH finds the row; INDEX fetches the price.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | ID | Product | Category | Price | Product | Price | |
| 2 | P-101 | Keyboard | Accessories | 45 | Laptop | 1,150 | |
| 3 | P-102 | Monitor | Displays | 210 | |||
| 4 | P-103 | Mouse | Accessories | 25 | |||
| 5 | P-104 | Laptop | Computers | 1,150 | |||
| 6 | P-105 | Webcam | Accessories | 60 |
- MATCH(F2, B2:B6, 0) finds Laptop in position 4.
- INDEX(D2:D6, 4) returns the 4th price: 1,150.
Example 3 — Two-way lookup
A table of monthly sales by region. Find the value where a chosen region and month meet.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar | Region | East | |
| 2 | North | 120 | 135 | 150 | Month | Feb | |
| 3 | East | 95 | 110 | 130 | Sales | 110 | |
| 4 | South | 140 | 125 | 160 | |||
| 5 | West | 80 | 90 | 105 |
One MATCH finds the row (East = 2), the other finds the column (Feb = 2). INDEX returns the crossing point.
Example 4 — Returning a whole column
Use 0 as the row number to get every row in a column — handy inside SUM.
This totals the March column (545). Change "Mar" to "Jan" and the total follows.
Example 5 — Lookup with multiple criteria
Find the price of an item matching both a category and a product name. Multiply the tests to get a 1 only where both are true, then MATCH the 1.
Returns 60. In older Excel, confirm with Ctrl+Shift+Enter.
Common mistakes
- #REF! errors. row_num or column_num is outside the range — e.g. row 8 of a 5-row range.
- Off-by-one results. The lookup range and the return range start on different rows. Make them line up (B2:B6 with D2:D6).
- Using MATCH without the 0. Without match_type 0, MATCH does an approximate match. Always include it for lookups.
Notes
- INDEX actually returns a reference, so =SUM(A2:INDEX(A2:A100, 10)) totals the first 10 cells.
- Related functions: MATCH, XMATCH, XLOOKUP, CHOOSECOLS, OFFSET.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.