The INDEX function returns the value at a specific row and column intersection within a given range. This guide explains how INDEX works and includes practical examples you can use immediately.
WHAT THE INDEX FUNCTION DOES
INDEX retrieves a value from a specific position in a range, like finding a cell by its coordinates. When combined with MATCH, it provides a more flexible alternative to VLOOKUP — it can look in any direction and doesn't require the lookup column to be first.
WHEN TO USE INDEX
- You want to retrieve a value from a specific position in a table
- You need more flexibility than VLOOKUP (lookup column anywhere)
- You're building dynamic references that change based on conditions
- You want to avoid the limitations of VLOOKUP's left-to-right searching
FORMULA SYNTAX
INDEX(array, row_num, [column_num])| Argument | Description |
|---|---|
array | The range of cells containing your data |
row_num | Which row number within the array (1 = first row) |
column_num | Which column number within the array (optional for single column) |
EXAMPLE 1: GET VALUE FROM SPECIFIC POSITION
We want to retrieve the price from row 3, column 2 of our product table.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Price | Stock |
| 2 | Widget A | $25 | 100 |
| 3 | Widget B | $30 | 50 |
| 4 | Widget C | $35 | 75 |
=INDEX(A2:C4, 2, 2)Result: $30 (row 2 within the array A2:C4, column 2 = Price of Widget B)
Need help applying this? Try this in Numstro Builder
EXAMPLE 2: DYNAMIC LOOKUP WITH INDEX AND MATCH
We want to look up any product's stock level (more flexible than VLOOKUP).
| A | B | C | |
|---|---|---|---|
| 1 | Product | Price | Stock |
| 2 | Widget A | $25 | 100 |
| 3 | Widget B | $30 | 50 |
| 4 | Widget C | $35 | 75 |
| 5 | |||
| 6 | Lookup: | Widget C | =INDEX(C2:C4,MATCH(B6,A2:A4,0)) |
=INDEX(C2:C4, MATCH("Widget C", A2:A4, 0))Result: 75 (MATCH finds "Widget C" in row 3, INDEX returns value from C2:C4 row 3)
Need help applying this? Try this in Numstro Builder
COMMON MISTAKES & EDGE CASES
- Row and column numbers start from 1 (not 0) within the specified array
- If you specify a single column range, column_num can be omitted
- Returns #REF! error if row or column number exceeds the array size
- INDEX/MATCH combination is more powerful than VLOOKUP for complex lookups
- Use absolute references ($A$2:$C$4) when copying formulas
RELATED FUNCTIONS
FURTHER READING
Official Microsoft documentation:
INDEX — Microsoft Support