The INDEX function in Excel returns a value from a specific position within a range or array. It is one of the most powerful lookup functions because it allows you to retrieve data based on row and column numbers. INDEX is commonly used in reports, employee databases, inventory systems, sales tracking, and advanced lookup formulas—especially when combined with the MATCH function.
In This Guide, You’ll Learn
- What the INDEX function does
- The syntax and arguments of INDEX
- How to return values from a list using row numbers
- How to retrieve values from tables using row and column positions
- How to combine INDEX with MATCH for flexible lookups
- Common mistakes to avoid when using INDEX
What is the INDEX Function?
The INDEX function returns a value from a specific cell within a selected range using row and column numbers.
INDEX Function Syntax
=INDEX(array, row_num, [column_num])Argument Definitions
- array – The range of cells or table you want Excel to search.
- row_num – The row number within the selected range that contains the value you want.
- column_num (optional) – The column number within the selected range. This is required when working with multiple columns.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> INDEX Function Data
Return a Value from a Single Column
Suppose you have a list of employee names and want to return the third employee in the list.
Formula Used
=INDEX(A2:A6,3)
How This Formula Works
- A2:A6 is the range containing employee names.
- 3 tells Excel to return the third value in the list.
- Excel returns Michael.
Return a Value from a Table
You can also use INDEX with rows and columns.
Suppose you have a product price table and want to return the price of the second product.
Formula Used
=INDEX(A2:B5,2,2)
How This Formula Works
- A2:B5 is the table range.
- 2 tells Excel to go to the second row of the range.
- 2 tells Excel to return the value from the second column.
- Excel returns 450.
Common INDEX Errors
Using a Row Number Outside the Range
If your row number is larger than the range, Excel returns a #REF! error.
Example:
=INDEX(A2:A5,10)This causes an error because the range only contains 4 rows.
Forgetting the Column Number
When using multiple columns, forgetting the column number can lead to unexpected results.
Incorrect:
=INDEX(A2:C10,2)Better:
=INDEX(A2:C10,2,3)Selecting the Wrong Range
Always make sure your lookup range includes the data you want returned.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> INDEX Function Data
