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