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 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:
- 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:
- 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.
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).
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.
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.
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, 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.
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 three arguments.
=INDEX (C8:F14,5,2)
Index function will return the value of the 5th row & 2nd Column, i.e., “333”.
Example #2 – Reference Form
In the below-mentioned example, I have three tables or different array or ranges of cells.
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)
- 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 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 –
