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).
 an empty cell in excel

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

fx icon in excel

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

 insert function tab in excel

4. Select Lookup & reference category.

5. Select INDEX function.

6. Then select ok.

excel INDEX function

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

Leave a Reply

Your email address will not be published. Required fields are marked *