When working with large tables in Excel, you often need to pull out specific values. The INDEX function makes this easy by returning the value of a cell at a given row and column.
In this guide, we’ll explain how the INDEX function works, go through step-by-step examples, and show you how to use it with the MATCH function for more advanced lookups.
🔍 What is the INDEX Function?
The INDEX function returns the value of a cell within a range based on its row and column number.
Syntax:
-
array → The range of cells you want to look in.
-
row_num → The row number within the array.
-
column_num (optional) → The column number within the array.
💡 If you only provide a row number, INDEX will return the value from that row in the first column.
✅ Example 1: Simple Row Lookup
Suppose you have a list of fruits.
| A1 |
|---|
| Apple |
| Banana |
| Cherry |
Formula:
Result: Banana
✔️ Explanation: INDEX looks at the second row of the range and returns “Banana.”
✅ Example 2: Row and Column Lookup
Now let’s look at a 2D table.
| A1 | B1 |
|---|---|
| Apple | Red |
| Banana | Yellow |
| Cherry | Red |
Formula:
Result: Yellow
✔️ Explanation: INDEX returns the value in the 2nd row and 2nd column → “Yellow.”
✅ Example 3: Using INDEX with MATCH
INDEX becomes very powerful when combined with MATCH. MATCH finds the position of a value, and INDEX retrieves it.
Example table:
| A1 | B1 |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Cherry | 30 |
Formula:
Result: 20
✔️ Explanation:
-
MATCH(“Banana”, A1:A3, 0) → 2 (Banana is in row 2).
-
INDEX(B1:B3, 2) → 20.
This is a VLOOKUP alternative that’s more flexible and efficient.
✅ Example 4: Returning Entire Rows or Columns
INDEX can also return an entire row or column when used in array formulas.
Formula (for Excel with dynamic arrays):
Result: Returns the entire 2nd row → {Banana, Yellow}.
🎯 Practical Uses of INDEX
-
Data Lookup → Retrieve values based on row and column positions.
-
Flexible Alternative to VLOOKUP → Works with data arranged both vertically and horizontally.
-
Dynamic Reporting → Combine with MATCH for user-driven dashboards.
-
Array Manipulation → Return entire rows or columns for analysis.
📝 Conclusion
The INDEX function is one of the most versatile tools in Excel. On its own, it helps retrieve values by position. When paired with MATCH, it becomes a powerful lookup solution that often outperforms VLOOKUP.
👉 If you want to build flexible and reliable spreadsheets, start practicing with INDEX—you’ll quickly see how it can save you time and improve accuracy.
