How to use INDEX in Excel
INDEX returns a value from a table when you tell it which row or column to use. For example, you can ask for the price in the second row. To find a row by a product name, combine INDEX with MATCH.
Syntax
=INDEX(array, row_num, [column_num])- array: the cells that contain your data.
- row_num: the row number to return from within the selected range.
- column_num: the column number to return when your selected range has more than one column.
Return a value by position
Enter these products and prices in cells A1:B4. To return the price on the second data row, use the formula below the table.
| Row | A: Product | B: Price |
|---|---|---|
| 1 | Product | Price |
| 2 | Keyboard | 25 |
| 3 | Mouse | 12 |
| 4 | Monitor | 180 |
=INDEX(B2:B4, 2)The answer is 12. The formula looks at the second cell in B2:B4, which is B3. INDEX counts from the start of the selected range, not from worksheet row 1.
Return a value from a two-dimensional range
For a range with several rows and columns, give INDEX both a row number and a column number. In B2:D4, this returns the value in the second row and third column of the selected range:
=INDEX(B2:D4, 2, 3)Build a lookup with INDEX and MATCH
Using the product and price table above, type Monitor into D2. This formula finds the product in column A and returns its price from column B:
=INDEX(B2:B4, MATCH(D2, A2:A4, 0))MATCH finds the product's position in A2:A4. INDEX uses that same position in B2:B4 to return the price. The two ranges need to contain the same number of rows. In newer Excel versions, XLOOKUP can do this in one formula.
Related guides: MATCH in Excel · LOOKUP in Excel · All tutorials.