EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

Excel INDEX Function

By Madhuri ThakurMadhuri Thakur

Home » Excel » Blog » Lookup & Reference Functions in Excel » Excel INDEX Function

INDEX Function in Excel

Excel INDEX Function (Table of Contents)

  • Introduction to Index Function in Excel
  • How to Use the INDEX Function in Excel?

Introduction to Index Function in Excel

INDEX Function in Excel is a pre-built integrated function that is categorized under Lookup & Reference Function.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Index 1

  • INDEX function in Excel helps you out to find out the value within a data set range, where it can look up values by both column & row.
  • To look up a value of cell where an intersection of the row and column happens.
  • It is commonly used along with the MATCH function, Where the combination of INDEX & MATCH function is an alternate option for the VLOOKUP function, which is more powerful & flexible.
  • INDEX formula or function returns a value from a tabular range or array based on the specified row and column number you specify.

The Formula or Syntax for INDEX function in Excel is:

Index syntax

  • Array form

It is used when referring to a cell is within a single range, e.g., A1:C12

= INDEX (array, row_num, [column_num])

  • Reference form

=INDEX (array or reference, row_num, [col_num], [area_num]) for three-way lookup.

When a reference to a cell is within multiple ranges, i.e., e.g., A1:C12, E1:G12

Syntax or formula for the INDEX Function in Excel has below-mentioned arguments:

Popular Course in this category
Sale
Excel Training (21 Courses, 9+ Projects)21 Online Courses | 9 Hands-on Projects | 110+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.9 (11,444 ratings)
Course Price

View Course

Related Courses
Excel Advanced Training (16 Courses, 23+ Projects)
  • Array: (Compulsory or required argument) It indicates the range or array of cells you want to look up, e.g. A1:C12

In the case of multiple ranges, it is a reference or array, where the individual range or areas is separated by commas and with closed brackets – e.g. (A1:C2, C4:D7) for reference form.

  • Row_num: (Compulsory or required argument) It is a row number from which you want to fetch the value.

Note: If there is only one row in an array or table range, then this argument will be optional.

  • Col_num: (Compulsory or required argument) It is a Column number from which you want to fetch the value

Note: if there is only one column in an array or table range, then this argument will be optional

  • Area_num (optional argument) – If the reference value is supplied as multiple ranges, area_num indicates which range to use. The order numbers areas they are specified.

Note: If the Area_num is left blank or if nothing is entered then, the INDEX Function considers as area 1 by default

How to Use the INDEX Function in Excel?

Let’s check out how the INDEX function works in Excel by using some examples.

You can download this INDEX Function Excel Template here – INDEX Function Excel Template

Example #1 – Array Form

In the below-mentioned example, the Table contains Employee name in column A (A2 to A8), Employee ID of employees in column B (B2 to B8), Gender of Employee in column C (C2 toC8) & Age of Employee in column D (D2 to D8).

Index Example 1-1

Suppose I want to find out the employee ID of Nishant with the help of the INDEX function in Excel.

Let’s apply the INDEX function in cell B11. Now, click on the Insert Function button (fx) under the formula section toolbar.

Index Example 1-2

Insert function dialog box will appear, type the keyword “INDEX” in Search for a function box, INDEX function will appear in select a function box

Now, Double click on the INDEX function.

Index Example 1-3

Now Select Arguments dialog box appears, where two types of INDEX function appears, here we are working on a single range or table; therefore, we need to select 1st one.

 Index Example 1-4

Once you select it, the function argument popup appears, where you need to enter the arguments of the INDEX function. = INDEX (array, row_num, [column_num])

Array: It is a table range or array of cells where you want to lookup, here you need to select table range, excluding the tabular column names, i.e., A2:D8.

 Index Example 1-5

Row_num: It is a row number from where I need to fetch the value, i.e., 5, the intersection will occur at the 5th row of the table

Index Example 1-6

Col_num: It is a column number from where I need to fetch the value, i.e., 2, the intersection will occur at the 2nd Column of the table

Click OK after entering all three arguments.

=INDEX (C8:F14,5,2)

Index Example 1-7

Index function will return the value of the 5th row & 2nd Column, i.e., “333”.

Index Example 1-7

Example #2 – Reference Form

In the below-mentioned example, I have three tables or different array or ranges of cells.

Index Function Example 2-1

Suppose I want to pull out the employee id details of Harshitha from table 2 with the help of the Index Function Reference Form.

Let’s apply the INDEX function in cell C21. Type or enter =INDEX( in that cell, two option of INDEX function appears, here, we need to enter the second INDEX function arguments.

  • Reference: It is a reference or array, where the individual table range or array needs to be mentioned separated by commas and with closed brackets, i.e. (A2:C8,A11:C13,A17:D18)

Index Function Example 2-2

  • Row_num: It is a row number from where I need to fetch the value, i.e., Employee ID details of Harshitha is present in table 2; therefore, we need to enter row number of it, i.e., 2, the intersection will occur at the 2nd row of the table
  • Col_num: It is a column number from where I need to fetch the value, i.e., 2, the intersection will occur at the 2nd Column of the 2nd table
  • Area_num: Here, there are multiple tables or ranges of cells; the area_num indicates which range or table to use. So, I need to use the 2nd table where the employee id details are present. i.e., 2

Index Function Example 2-3

INDEX function in excel returns the reference to row number 2 and column number 2 of the second area or table range or array A11:C13, which refers to cell H6. For example, the cell value of B21 is 520, which is an employee ID of Harshitha.

Things to Remember About the INDEX Function in Excel

  • Either row_num / column number is Compulsory or mandatory; otherwise, the INDEX function returns #VALUE! error, it can also occur if both the value is entered zero or if both the parameters left blank
  • Suppose the entered row_num, col_num, or area_num arguments are non-numeric values. Then #VALUE! error Occurs
  • #REF! the error occurs if the Col_num argument is greater than the number of columns in the table or array range or Row_num argument is greater than the number of rows in the table or array range
  • #REF! the error occurs when the area_num argument is greater than or more than the number of areas in the supplied range.
  • If the area argument or parameter is mentioned in the INDEX Formula in excel is referenced from any other sheet. Then the Index Function in Excel returns #VALUE! Error.
  • Area argument or parameter mentioned in the INDEX Formula in excel must be located in that sheet itself.

Recommended Articles

This is a guide to INDEX Function in Excel. Here we discuss how to use the INDEX Function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Excel INDEX Function
  2. INDEX MATCH Function in Excel
  3. Time Function in Excel
  4. VBA Color Index

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • Lookup Reference Functions in Excel
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (23+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (21+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download INDEX Function Excel Template

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

डाउनलोड INDEX Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - Online EXCEL COURSE Learn More