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
- 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!