Excel INDEX Function

What is INDEX function in Excel?

The INDEX function is one of the Lookup & reference functions of Excel.

It Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

We can find this function in Lookup & reference of insert function Tab.

How to use INDEX function in excel

1. Click on an empty cell (like F5).

2. Click on the fx icon (or press shift+F3).

3. In the insert function tab you will see all functions.

4. Select Lookup & reference category.

5. Select INDEX function.

6. Then select ok.

7. In the function arguments Tab, you will see INDEX function.

8. Array Is a range of cells or an array constant.

9. Row_num selects the row in Array or Reference from which to return a value. if omitted, Column_num is required.

10. Column_num selects the Column in Array or Reference from which to return a value. if omitted, Row_num is required.

11. You will see the result in the formula result section.

=INDEX(Table1,2,3) ----->>>>answer is  205

Examples of INDEX function in excel

Example 1:

How many ways are to use the INDEX function?

we can use two ways to use index function:

1 . Array form

Returns the value of an element in a table or an array, selected by the row and column number indexes.

Use the array form if the first argument to INDEX is an array constant.

=INDEX({"a","b";"c","d"},2,2) ----->>>>answer is  d

2 . Reference form

Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of non-adjacent selections, you can pick the selection to look in.

=INDEX((A2:E5, A7:E10),2,3,2)----->>>>answer is  202

Example 2:

What happens If you set row_num or column_num to zero in INDEX function?

If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula.

=INDEX(Table1,0,0)----->>>>answer is Table1

If you set row_num to 0 (zero), INDEX returns the array of values for the entire column, respectively.

=INDEX(Table1,0,3)----->>>>answer is Column 3

If you set column_num to 0 (zero), INDEX returns the array of values for the entire row, respectively.

=INDEX(Table1,2,0)----->>>>answer is Row 2

What is the purpose of INDEX function?

It Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

What is the Return value of INDEX function?

It can return any type of data (number, text,…)

INDEX (Array, Row_num, Column_num)------>>>> any type of data

How many arguments does INDEX function have?

INDEX (Array, Row_num, Column_num)

This function has just 3 Arguments.

Array Is a range of cells or an array constant.

Row_num selects the row in Array or Reference from which to return a value. if omitted, Column_num is required.

Column_num selects the Column in Array or Reference from which to return a value. if omitted, Row_num is required.

Row_num argument of INDEX function is required and not optional.

Column_num of INDEX function is not required and is optional.

Which version of excel supports INDEX function?

This function is available for all excel versions (2003-2019)

Errors in INDEX function

row_num and column_num must point to a cell within array; otherwise, INDEX returns a #REF! error.

=INDEX(B1,2,2)----->>>>answer is #REF!

INDEX related functions 

• MATCH Function
• VLOOKUP Function
• HLOOKUP Function
• LOOKUP Function
• XLOOKUP Function