Excel INDEX Function (Table of Contents)
Introduction to Index Function in Excel
INDEX Function in Excel is a pre-built integrated function that is categorized under Lookup & Reference Function.
- INDEX function in Excel helps you out to find out 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 MATCH function, Where the combination of INDEX & MATCH function is an alternate option for 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:
- Array form
It is used when reference 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 a multiple range i.e. e.g. A1:C12, E1:G12
Syntax or formula for the INDEX Function in Excel has below-mentioned arguments:
- Array: (Compulsory or required argument) It indicates the range or array of cells you want to lookup e.g. A1:C12
In 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. Areas are numbered by the order 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 INDEX Function in Excel?
Let’s check out how the INDEX function works in Excel by using some examples.
Example #1 – Array Form
In the below-mentioned example, 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).
Suppose I want to find out the employee ID of Nishant with the help of INDEX function in Excel.
Let’s apply INDEX function in cell B11. Now, click on Insert Function button (fx) under formula section toolbar.
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 INDEX function.
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
Once, you select it, function argument popup appears, where you need to enter the arguments of 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.
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
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 the three arguments.
=INDEX (C8:F14,5,2)
Index function will return the Value of 5th row & 2nd Column i.e. “333”.
Example #2 – Reference Form
In the below-mentioned example, I have three tables or different array or range of cells.
Suppose I want to pull out the employee id details of Harshitha from table 2 with the help of Index Function Reference Form.
Let’s apply INDEX function in the 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)
- 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 table 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 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. 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, INDEX function returns #VALUE! error, it can also occur if both the value is entered zero or if both the parameters left blank
- If the entered row_num, col_num or area_num arguments are non-numeric value. then #VALUE! error Occurs
- #REF! error occurs if 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! error occurs when 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 INDEX Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
