Introduction
The INDEX function is an important Excel lookup function. It helps you retrieve a value from a specific position in a table or range. In this tutorial, you will learn how INDEX works, understand its arguments, and practice retrieving information from employee, sales, student, and product records.
What Is the INDEX Function in Excel?
The INDEX function returns a value from a specific row and column position within a selected range.
Imagine you have a table containing employee names, departments, and salaries. If you want to retrieve the salary of the employee located in the third row of your selected data range, INDEX can return that value.
In simple words:
- First, select the range containing your data.
- Specify the row number.
- Specify the column number when working with multiple columns.
- Excel returns the value from that position.
INDEX Function Syntax
=INDEX(array, row_num, [column_num])
Understanding the Arguments
| Argument | Meaning |
|---|---|
| array | The range of cells from which you want to retrieve a value. |
| row_num | The position of the row from which you want the result. |
| column_num | The position of the column from which you want the result. This is optional when the selected range has only one column. |
Important: INDEX counts rows and columns within the selected range, not by the worksheet’s actual row and column numbers.
For example, if your selected range is A2:C6, the first row of that range is row position 1, even though it is on worksheet row 2.
How Does the INDEX Function Work?
Suppose you have the following employee data:
| Employee ID | Name | Department | Salary |
|---|---|---|---|
| E101 | Rahul | IT | 35000 |
| E102 | Priya | HR | 40000 |
| E103 | Aman | Sales | 45000 |
| E104 | Neha | Finance | 50000 |
| E105 | Karan | IT | 55000 |
Now consider this formula:
=INDEX(B2:D6,3,2)
Let’s understand how Excel evaluates it:
B2:D6is the selected range.3tells Excel to select the third row within that range.2tells Excel to select the second column within that range.- Excel returns the value found at that position.
The selected position is:
| Name | Department | Salary |
|---|---|---|
| Rahul | IT | 35000 |
| Priya | HR | 40000 |
| Aman | Sales | 45000 |
| Neha | Finance | 50000 |
| Karan | IT | 55000 |
Expected Result: Sales
The formula returns Sales because it is located in the third row and second column of the selected range.
Practical Examples of INDEX Function in Excel
Let’s practice the INDEX function using different business and educational situations.
Question 1: Retrieve an Employee Name
Problem Statement
An HR department maintains employee records. The HR manager wants to retrieve the name of the employee located in the third position of the employee list.
Data Table
| Employee ID | Employee Name | Department |
|---|---|---|
| E101 | Rahul | IT |
| E102 | Priya | HR |
| E103 | Aman | Sales |
| E104 | Neha | Finance |
| E105 | Karan | IT |
Excel Formula
=INDEX(B2:B6,3)
Expected Result
| Result |
|---|
| Aman |
Explanation
B2:B6is the employee name range.3represents the third position in the selected range.- Excel returns Aman.
Concept Covered
Using INDEX with a single-column range. When the selected range contains only one column, you only need to specify the row position.
Question 2: Retrieve an Employee Salary
Problem Statement
The HR manager wants to retrieve the salary of the employee located in the fourth position of the employee records.
Data Table
| Employee ID | Employee Name | Department | Salary |
|---|---|---|---|
| E101 | Rahul | IT | 35000 |
| E102 | Priya | HR | 40000 |
| E103 | Aman | Sales | 45000 |
| E104 | Neha | Finance | 50000 |
| E105 | Karan | IT | 55000 |
Excel Formula
=INDEX(D2:D6,4)
Expected Result
| Result |
|---|
| 50000 |
Explanation
D2:D6contains employee salaries.4tells Excel to select the fourth salary.- The fourth salary is 50000.
Concept Covered
Retrieving numerical values from a single-column range using a row position.
Question 3: Retrieve a Value from a Specific Row and Column
Problem Statement
A company maintains monthly sales records for different products. The manager wants to retrieve the sales amount for Laptop in the month of February.
Data Table
| Product | January | February | March |
|---|---|---|---|
| Laptop | 50000 | 60000 | 70000 |
| Mobile | 30000 | 35000 | 40000 |
| Tablet | 20000 | 25000 | 30000 |
| Printer | 15000 | 18000 | 22000 |
Excel Formula
=INDEX(B2:D5,1,2)
Expected Result
| Result |
|---|
| 60000 |
Explanation
B2:D5contains the monthly sales figures.1selects the first row of the selected range, which represents Laptop.2selects the second column, which represents February.- Excel returns 60000.
Concept Covered
Using both row_num and column_num to retrieve a value from a two-dimensional range.
Question 4: Retrieve a Student’s Marks
Problem Statement
A school maintains students’ marks in three subjects. The teacher wants to retrieve the English marks of the third student.
Data Table
| Student Name | Mathematics | English | Science |
|---|---|---|---|
| Aarav | 85 | 78 | 90 |
| Riya | 92 | 88 | 95 |
| Mohit | 75 | 82 | 80 |
| Simran | 89 | 91 | 87 |
| Kabir | 80 | 76 | 84 |
Excel Formula
=INDEX(B2:D6,3,2)
Expected Result
| Result |
|---|
| 82 |
Explanation
B2:D6is the marks range.3selects the third student’s row.2selects the English column within the selected range.- Excel returns 82.
Concept Covered
Selecting a specific subject’s marks using row and column positions.
Question 5: Retrieve a Product Price
Problem Statement
An electronics store maintains product prices. The store manager wants to retrieve the price of the fourth product in the product list.
Data Table
| Product ID | Product Name | Price |
|---|---|---|
| P101 | Laptop | 55000 |
| P102 | Keyboard | 1200 |
| P103 | Mouse | 600 |
| P104 | Monitor | 12000 |
| P105 | Printer | 8500 |
Excel Formula
=INDEX(C2:C6,4)
Expected Result
| Result |
|---|
| 12000 |
Explanation
C2:C6contains product prices.4selects the fourth position in the price range.- Excel returns 12000, which is the price of the Monitor.
Concept Covered
Retrieving product prices using a position within a selected range.
Question 6: Retrieve Sales from a Specific Region and Product
Problem Statement
A company maintains sales records for different regions and products. The sales manager wants to retrieve the sales amount for Mobile in the South region.
Data Table
| Region | Laptop | Mobile | Tablet |
|---|---|---|---|
| North | 80000 | 45000 | 25000 |
| South | 75000 | 50000 | 30000 |
| East | 65000 | 40000 | 22000 |
| West | 90000 | 55000 | 35000 |
Excel Formula
=INDEX(B2:D5,2,2)
Expected Result
| Result |
|---|
| 50000 |
Explanation
B2:D5contains sales amounts.2selects the second region’s row, which is South.2selects the second product column, which is Mobile.- Excel returns 50000.
Concept Covered
Using row and column positions together to retrieve a value from a business report.
Question 7: Retrieve a Department’s Budget
Problem Statement
A company maintains department-wise budgets for different quarters. The finance manager wants to retrieve the Marketing department’s budget for Quarter 3.
Data Table
| Department | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| IT | 100000 | 110000 | 120000 | 130000 |
| HR | 50000 | 55000 | 60000 | 65000 |
| Marketing | 80000 | 85000 | 95000 | 100000 |
| Finance | 70000 | 75000 | 80000 | 90000 |
Excel Formula
=INDEX(B2:E5,3,3)
Expected Result
| Result |
|---|
| 95000 |
Explanation
B2:E5contains the quarterly budgets.3selects the third department’s row, which is Marketing.3selects the third quarter’s column, which is Q3.- Excel returns 95000.
Concept Covered
Retrieving a value from a larger business report by identifying its row and column positions.
Student Practice Tasks
Try solving these tasks yourself using the INDEX function.
Task 1: Employee Department
| Employee ID | Name | Department |
|---|---|---|
| E201 | Arjun | IT |
| E202 | Meera | HR |
| E203 | Dev | Sales |
| E204 | Pooja | Finance |
Retrieve the department of the third employee.
Task 2: Product Price
| Product | Price |
|---|---|
| Laptop | 55000 |
| Mobile | 25000 |
| Tablet | 18000 |
| Printer | 12000 |
Retrieve the price of the second product.
Task 3: Student Marks
| Student | English | Maths | Science |
|---|---|---|---|
| Ankit | 75 | 80 | 85 |
| Neha | 88 | 92 | 90 |
| Rahul | 70 | 78 | 82 |
| Simran | 95 | 89 | 93 |
Retrieve the Maths marks of the fourth student.
Final Student Challenge: Create an Employee Information Report
Create the following employee table in Excel.
| Employee ID | Name | Department | Salary | City |
|---|---|---|---|---|
| E101 | Rahul | IT | 35000 | Delhi |
| E102 | Priya | HR | 40000 | Noida |
| E103 | Aman | Sales | 45000 | Gurgaon |
| E104 | Neha | Finance | 50000 | Delhi |
| E105 | Karan | IT | 55000 | Noida |
| E106 | Simran | Marketing | 48000 | Gurgaon |
Your Tasks
- Retrieve the name of the fifth employee.
- Retrieve the salary of the third employee.
- Retrieve the department of the sixth employee.
- Retrieve the city of the second employee.
- Retrieve the salary of the fourth employee using both row and column positions.
Challenge Goal: Practice selecting values from different rows and columns using INDEX without manually searching through the table.
Key Takeaways
- INDEX retrieves a value from a specific position in a range.
- The
arrayargument defines the selected data range. row_numidentifies the required row position.column_numidentifies the required column position.- For a single-column range, the column number is not required.
- Row and column positions are counted within the selected range, not the entire worksheet.
- INDEX is useful for retrieving information from employee records, sales reports, student marks, and business tables.
Frequently Asked Questions (FAQs)
1. What is the INDEX function in Excel?
The INDEX function returns a value from a specific row and column position within a selected range.
2. What is the syntax of the INDEX function?
The basic syntax is:
=INDEX(array, row_num, [column_num])
3. What is the purpose of the array argument?
The array argument specifies the range of cells from which Excel should retrieve the required value.
4. Is the column_num argument compulsory?
No. The column_num argument is optional when working with a single-column range. It is needed to identify a column when selecting from a multi-column range.
5. Does INDEX count the actual Excel row numbers?
No. INDEX counts positions within the selected range. For example, in A5:A10, the first position is cell A5, not worksheet row 5.
6. Can INDEX retrieve values from multiple columns?
Yes. INDEX can retrieve values from a multi-column range by specifying both the row number and column number.
7. What happens if the row number is greater than the selected range?
Excel returns a #REF! error when the specified row position is outside the selected range.
8. Can INDEX retrieve text and numbers?
Yes. INDEX can return text, numbers, dates, and other values stored in the selected range.
9. What is the difference between a single-column and a two-dimensional INDEX formula?
A single-column formula generally needs only the range and row position. A two-dimensional formula uses both row and column positions to identify the required value.
10. Is INDEX useful for business reports?
Yes. INDEX is useful for retrieving specific information from employee records, product lists, sales reports, student records, and other structured data.

