One of the most powerful and flexible functions in Excel is INDEX. Whether you need to retrieve values from a range, work dynamically with large datasets, or combine it with other functions for complex operations, INDEX is essential. In this article, I’ll walk you through how INDEX works in Excel and provide the best INDEX examples to help you master it.
What is the INDEX Function in Excel?
The INDEX function retrieves the value from a specified position within a given range or array. It works by specifying the row and column numbers of the value you need.
There are two forms of the INDEX function:
- Array Form: Returns the value from a specified row and column within an array.
- Reference Form: Returns a reference to a cell instead of the value itself.
Basic Syntax of INDEX
The syntax of INDEX depends on whether you’re using the array form or the reference form.
1. Array Form
=INDEX(array, row_num, [column_num])array– The range of cells or array from which to retrieve a value.row_num– The row number within the array.column_num(optional) – The column number within the array.
2. Reference Form
=INDEX(reference, row_num, [column_num], [area_num])reference– A range of multiple areas.row_num– The row number within the selected area.column_num(optional) – The column number within the selected area.area_num(optional) – The area number to use (needed when referencing multiple non-adjacent ranges).
Best INDEX Examples
1. Returning a Single Value
Let’s say we have a dataset in range A1:C3:
| A | B | C |
|---|---|---|
| Apple | Banana | Cherry |
| Dog | Elephant | Fox |
| Green | Blue | Red |
=INDEX(A1:C3, 2, 3)This formula returns “Fox” because it’s the value in the 2nd row and 3rd column of the range.
2. Using INDEX with a Single Column
If you want to retrieve an item from a single column, you only need to specify the row:
=INDEX(A1:A3, 2)This returns “Dog” from the second row of column A.
3. Dynamic Lookup with MATCH
One of the best uses of INDEX is in combination with MATCH. The MATCH function helps find a row or column number dynamically.
=INDEX(A1:C3, MATCH("Dog", A1:A3, 0), 2)Here’s what happens:
MATCH("Dog", A1:A3, 0)finds the row number where “Dog” appears, which is 2.INDEX(A1:C3, 2, 2)then retrieves the value in row 2, column 2, which is Elephant.
4. Retrieving the Last Value in a Column
To dynamically get the last value in a column, use:
=INDEX(A:A, COUNTA(A:A))This counts the number of non-empty cells in column A and retrieves the last value.
Why Use INDEX Instead of VLOOKUP?
The INDEX function has several advantages over VLOOKUP:
- Flexibility: Works with both vertical and horizontal data.
- Non-restrictive: It does not require the lookup column to be first, unlike
VLOOKUP. - Efficiency: Faster for large datasets as it does not scan entire columns.
- Robustness: Less prone to breaking when columns are inserted or removed.
Final Thoughts
Understanding how INDEX works in Excel opens the door to more efficient and dynamic spreadsheet usage. It’s a fundamental function for data retrieval, especially when paired with MATCH or used in advanced reporting scenarios. Mastering these best INDEX examples will help you navigate Excel with confidence.
Other interesting article:
How XLOOKUP works in Excel? Best XLOOKUP examples