Syntax
=INDEX(array, row_num, [column_num])| Argument | What it means |
|---|---|
array | The range of cells. |
row_num | Which row of the range. |
column_num | Which column. Leave it out for a single column. |
Examples with results
Every result below is calculated by the practice engine on the sample data shown, not typed in by hand.
Price list (fictional)
| Row | A | B | C |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P-101 | Desk lamp | 24.9 |
| 3 | P-102 | Notebook | 3.5 |
| 4 | P-103 | Mouse | 15 |
| 5 | P-104 | Keyboard | 32 |
| 6 | P-105 | Monitor stand | 21 |
=INDEX(C2:C6,3)The third price
=INDEX(A2:C6,3,2)Row 3, column 2 of the table
=INDEX(C2:C6,MATCH("Keyboard",B2:B6,0))INDEX with MATCH: the price of the Keyboard
Try it yourself
Change the formula or switch to other sample data.
Press Calculate to see the result.
The practice box runs in your browser with BDK’s own calculator, which covers about 60 common functions. Cell references work as in Excel (B2, B2:B9). Your formulas are not saved or sent anywhere. For anything unusual, check the result in Excel itself.
How people use INDEX at work
- HR
Pick the department of the person with the highest salary.
Data: Team list (fictional)=INDEX(B2:B8,MATCH(MAX(C2:C8),C2:C8,0)) - General office
Two-way lookup in a price grid.
Data: Delivery price by zone and weight (fictional)=INDEX(B2:D5,MATCH(10,A2:A5,0),MATCH("Zone B",B1:D1,0))
Common mistakes and tips
- INDEX with MATCH is the classic way to look to the left of a column, which VLOOKUP cannot do.
- The row and column numbers count from the top-left of the range you give, not of the sheet.
Works in
All versions of Excel, and Google Sheets.
Need Something Different? Let’s Talk.
Looking for different features or something built around your workflow? Share your idea with us.