INDEX function
Quick answer
INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.
- Syntax
=INDEX(array, row_num, [column_num])- Most common use
=INDEX(A2:D5,3,4)returns 498 (Row 3, column 4 of the table: Columbus in Q2)
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 14, 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
Answers that use INDEX
In other languages
INDEX is INDICE in Spanish Excel and INDEX in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .