INDEX Function in Excel Tutorial

Excel Tutorial

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

ArgumentMeaning
arrayThe range of cells from which you want to retrieve a value.
row_numThe position of the row from which you want the result.
column_numThe 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 IDNameDepartmentSalary
E101RahulIT35000
E102PriyaHR40000
E103AmanSales45000
E104NehaFinance50000
E105KaranIT55000

Now consider this formula:

=INDEX(B2:D6,3,2)

Let’s understand how Excel evaluates it:

  1. B2:D6 is the selected range.
  2. 3 tells Excel to select the third row within that range.
  3. 2 tells Excel to select the second column within that range.
  4. Excel returns the value found at that position.

The selected position is:

NameDepartmentSalary
RahulIT35000
PriyaHR40000
AmanSales45000
NehaFinance50000
KaranIT55000

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 IDEmployee NameDepartment
E101RahulIT
E102PriyaHR
E103AmanSales
E104NehaFinance
E105KaranIT

Excel Formula

=INDEX(B2:B6,3)

Expected Result

Result
Aman

Explanation

  • B2:B6 is the employee name range.
  • 3 represents 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 IDEmployee NameDepartmentSalary
E101RahulIT35000
E102PriyaHR40000
E103AmanSales45000
E104NehaFinance50000
E105KaranIT55000

Excel Formula

=INDEX(D2:D6,4)

Expected Result

Result
50000

Explanation

  • D2:D6 contains employee salaries.
  • 4 tells 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

ProductJanuaryFebruaryMarch
Laptop500006000070000
Mobile300003500040000
Tablet200002500030000
Printer150001800022000

Excel Formula

=INDEX(B2:D5,1,2)

Expected Result

Result
60000

Explanation

  • B2:D5 contains the monthly sales figures.
  • 1 selects the first row of the selected range, which represents Laptop.
  • 2 selects 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 NameMathematicsEnglishScience
Aarav857890
Riya928895
Mohit758280
Simran899187
Kabir807684

Excel Formula

=INDEX(B2:D6,3,2)

Expected Result

Result
82

Explanation

  • B2:D6 is the marks range.
  • 3 selects the third student’s row.
  • 2 selects 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 IDProduct NamePrice
P101Laptop55000
P102Keyboard1200
P103Mouse600
P104Monitor12000
P105Printer8500

Excel Formula

=INDEX(C2:C6,4)

Expected Result

Result
12000

Explanation

  • C2:C6 contains product prices.
  • 4 selects 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

RegionLaptopMobileTablet
North800004500025000
South750005000030000
East650004000022000
West900005500035000

Excel Formula

=INDEX(B2:D5,2,2)

Expected Result

Result
50000

Explanation

  • B2:D5 contains sales amounts.
  • 2 selects the second region’s row, which is South.
  • 2 selects 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

DepartmentQ1Q2Q3Q4
IT100000110000120000130000
HR50000550006000065000
Marketing800008500095000100000
Finance70000750008000090000

Excel Formula

=INDEX(B2:E5,3,3)

Expected Result

Result
95000

Explanation

  • B2:E5 contains the quarterly budgets.
  • 3 selects the third department’s row, which is Marketing.
  • 3 selects 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 IDNameDepartment
E201ArjunIT
E202MeeraHR
E203DevSales
E204PoojaFinance

Retrieve the department of the third employee.

Task 2: Product Price

ProductPrice
Laptop55000
Mobile25000
Tablet18000
Printer12000

Retrieve the price of the second product.

Task 3: Student Marks

StudentEnglishMathsScience
Ankit758085
Neha889290
Rahul707882
Simran958993

Retrieve the Maths marks of the fourth student.

Final Student Challenge: Create an Employee Information Report

Create the following employee table in Excel.

Employee IDNameDepartmentSalaryCity
E101RahulIT35000Delhi
E102PriyaHR40000Noida
E103AmanSales45000Gurgaon
E104NehaFinance50000Delhi
E105KaranIT55000Noida
E106SimranMarketing48000Gurgaon

Your Tasks

  1. Retrieve the name of the fifth employee.
  2. Retrieve the salary of the third employee.
  3. Retrieve the department of the sixth employee.
  4. Retrieve the city of the second employee.
  5. 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 array argument defines the selected data range.
  • row_num identifies the required row position.
  • column_num identifies 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.