INDEX function
INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column. Paired with MATCH, which finds the row, it is the most flexible lookup in Excel: it works in every version, looks in any direction and doesn't break when columns move.
- Available in
- Every current version
- Category
- Lookup functions
- Returns
- The value at that position, or a whole column or row when row_num or column_num is 0.
- Google Sheets
- Works the same way in Google Sheets.
Syntax
=INDEX(array, row_num, [column_num])
| Argument | Required? | What it is |
|---|---|---|
array | Required | The range to pick from. |
row_num | Required | The row within the range, counting from 1. Use 0 to return the whole column. |
column_num | Optional | The column within the range, counting from 1. Leave it out for a single-column range. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =INDEX(A2:D5,3,4) | 498 | Row 3, column 4 of the table: Columbus in Q2 |
| F3 | =INDEX(C2:C5,MATCH("Boise",A2:A5,0)) | 230 | With MATCH: Boise's Q1 figure |
| F4 | =INDEX(A2:A5,MATCH(MAX(D2:D5),D2:D5,0)) | Columbus | Which city had the highest Q2, found by value instead of name |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| City | Region | Q1 | Q2 | Result | |
| Austin | South | 410 | 455 | 498 | |
| Boise | West | 230 | 262 | 230 | |
| Columbus | Midwest | 515 | 498 | Columbus | |
| Durham | South | 305 | 341 |
Practice INDEX
From lesson 11, INDEX & MATCH. Graded instantly, then re-checked on changed data.
Two-way lookup
H6, return the shipping rate for the zone in H2 and the weight in H3.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #REF! | row_num or column_num is outside the range. INDEX(A2:D5,5,1) fails because the range has only four rows. |
| Off-by-one results | Positions count from the first cell of the range, not from row 1 of the sheet. If the range starts at row 2, row 1 of the range is sheet row 2. |
| INDEX and MATCH ranges don't line up | Both ranges must start on the same row. INDEX(C2:C5,MATCH(x,A3:A6,0)) returns the row below the one you want. |
Tips
- Two-way lookup: =INDEX(C2:D5,MATCH("Austin",A2:A5,0),MATCH("Q2",C1:D1,0)).
- =SUM(INDEX(C2:D5,0,2)) totals the second column, because row_num 0 means every row.
Questions
Why use INDEX MATCH instead of VLOOKUP?
It can return columns to the left of the search column, it doesn't depend on a column number that breaks when columns move, and it works in every version of Excel.
Is INDEX MATCH better than XLOOKUP?
XLOOKUP is simpler to write. INDEX/MATCH is the better choice when the file must work in Excel 2019 or earlier, and it handles two-way lookups by row and column neatly.
Lessons that use INDEX
- Lesson 11: INDEX & MATCH (Pro)
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.