__Excel INDEX Function in Array Form (Quick View):__

To return a value (or values) from a single range, use the array form of the **INDEX **function.

__Excel INDEX Function in Reference Form (Quick View):__

To return a value (or values) from multiple ranges, use the reference form of the **INDEX **function.

## Introduction to the INDEX Function in Excel

__Objective:__

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

__Syntax of the INDEX Function in Array Form:__

**=INDEX (array, row_num, [column_num])**

**Arguments:**

argument | required/ optional | value |
---|---|---|

array | Required | Pass a range of cells, or an array constant to this argument |

row_num | Required | Pass the row number in the cell range or the array constant |

col_num | Optional | Pass the column number in the cell range or the array constant |

**Note:**

- If you use both the
**row_num**and**column_num**arguments, the**INDEX**function will return the value from the cell at the intersection of the**row_num**and**column_num**. - If you set
**row_num**or**column_num**to 0 (zero), then you will get the whole column values or the whole row values in the form of arrays.

__Syntax of the INDEX Function in Reference Form:__

**=INDEX (reference, row_num, [column_num], [area_num])**

**Arguments:**

argument | required/ optional | value |
---|---|---|

reference | Required | Pass more than one range or array |

row_num | Required | Pass the row number in a specific cell range |

col_num | Optional | Pass the column number in a specific cell range |

area_num | Optional | Pass the area number that you want to select from a group of ranges |

**Note:**

- If you pass more than one range or array as the array value, you should also pass the area_num.
- If the
**area_num**is absent, the**INDEX**Function will work with the first range. If you pass a value as the**area_num**, the**INDEX**function will work in that specific range.

### Example 1 – Select an Item from a List

__One Dimensional List with a Single Column:__

To retrieve the 3rd product from the list, use the following formula in **C13**, specifying the row number in** C12**.

`=INDEX(B5:B10,C12)`

Or,

`=INDEX(B5:B10,3)`

__One Dimensional List with a Single Row:__

To retrieve an item from a single row using the **INDEX** function, specify the serial number in **column B** and enter the following formula in **C20**:

`=INDEX(C17:H17,,B20)`

Or,

`=INDEX(C17:H17,3)`

You can also enter the serial number directly in the formula instead of using a cell reference.

__Retrieve an Item from a Multidimensional List:__

To retrieve an item from a list with multiple dimensions, specify the row and column number in the **INDEX **function.

To get the item from the **3rd row** and **4th column **of the list, enter the following formula in **C33**.

`=INDEX(C26:H29,C31,C32)`

**Note:**

- If you specify a row number beyond the range of your list, it will cause a
**#REF! error**. - You can also refer to an array as a reference and apply the
**INDEX**function. For example, the formula**=INDEX({1,2,3;4,5,6;7,8,9;10,11,12},2,3)**will return 8. The**array constant****{1,2,3;4,5,6;7,8,9;10,11,12}**contains columns separated by semicolons.

### Example 2 – Selecting an Item from Multiple Lists

With the **[area_num]**, you can input multiple arrays or reference ranges in the INDEX function and specify from which array the function will return an item or value.

There are two lists in the dataset below, one for **Windows** and the other for **MS Office.** Use the following formula to get a value in the **Windows** list.

`=INDEX((D5:G9,I5:L9),C11,E11,1)`

Or,

`=INDEX((D5:G9,I5:L9),C11,E11,2)`

to get an item in the **MS Office** list.

**Note:**

If you don’t specify the number in this formula, Excel will consider area 1 to return the value.

### Example 3 – Combine the MATCH Function with the INDEX Function to Match Multiple Criteria and Return the Value

**The MATCH function** returns the relative position of an item in an array that matches a specified value in a specified order.

To match criteria specified in **C12** and **C13.**

**Steps:**

- Enter the following formula in
**C14**:

`=INDEX(B5:E10,MATCH(C13,B5:B10,0),MATCH(C12,B4:E4,0))`

- Press
**ENTER.**

** Formula Breakdown**

**MATCH(C12,B4:E4,0)**

**Output:** 3

takes input from **C12** and performs an exact match in **B4:E4**. 0 in the last argument indicates an exact match. Since the item in **C12** is in the third column of **B4:E4**, the function returns 3.

**MATCH(C13,B5:B10,0)**

**Output:** 3

the function works row-wise in **B5:B10,** which means the items are in different rows but in one single column.

**INDEX(B5:E10,MATCH(C13,B5:B10,0),MATCH(C12,B4:E4,0))**

**Output:**1930

the INDEX function will check row 3 and column 3 within B5:E10 and return a value in the row-column intersection.

### Example 4 – Combining the INDEX, MATCH, and IF Functions to Match Multiple Criteria from Two Lists

The dataset showcases Sales data for **Windows** and **MS Office** in different countries and years.

Set 3 criteria: **Product**, **Year,** and **Country, **to get sales data.

**Steps:**

- The criteria set are:
**Year: 2019**,**Product: MS Office**, and**Country: Canada,**in**C11, C12,**and**C13**. - Enter the following formula
**Cell C14**and press**ENTER.**

`=INDEX(INDEX((D5:G9,I5:L9),,,IF(C12="Windows",1,2)),MATCH(C13,B5:B9,0),MATCH(C11,INDEX((D5:G5,I5:L5),,,IF(C12="Windows",1,2)),0))`

- You will see the corresponding sales data in
**C14**. - Make the formula more dynamic, using
**data validation**.

** Formula Breakdown**

**IF(C12=”Windows”,1,2))**

**Output**: 2

**C12** contains **Windows**, the criteria is not matched and the **IF function** returns 2.

**INDEX((D5:G9,I5:L9),,,IF(C12=”Windows”,1,2))**

**Output**: {2017,2018,2019,2020;8545,8417,6318,5603;5052,8052,5137,5958;9590,6451,3177,6711;5126,3763,3317,9940}

the **IF(C12=”Windows”,1,2)** part returns 2, so it becomes **INDEX((D5:G9,I5:L9),,,2)**. The **INDEX** function returns the second range assigned to it.

**MATCH(C11,INDEX((D5:G5,I5:L5),,,IF(C12=”Windows”,1,2)),0)**

**Output**: 3

**IF(C12=”Windows”,1,2)** part returns 2, so it becomes **MATCH(C11,INDEX((D5:G5,I5:L5),,,2),0). ****INDEX((D5:G5,I5:L5),,,2)** returns **I5:G5,** which is **{2017,2018,2019,2020}**. The **MATCH** formula becomes **MATCH(C11,{2017,2018,2019,2020},0)**, and returns 3 since the value 2019 in **C11** is in the 3rd position of the {2017,2018,2019,2020} array.

**MATCH(C13,B5:B9,0),**

**Output**: 4

the **MATCH** function matches the value of **C13** in **B5:B9** range and returns 4: the position of “Canada” in **B5:B9**.

**=INDEX({2017,2018,2019,2020;8545,8417,6318,5603;5052,8052,5137,5958;9590,6451,3177,6711;5126,3763,3317,9940},4,3)**

**Output**: 3177

returns the value at the intersection the 4th row and 3rd column.

### Example 5 – Returning a Row or Column Entirely from a Range

The **INDEX** function returns a row or column from a range.

**Steps:**

- To return the first row in the
**Windows**list, enter the following formula in any cell (here,**F11**), and press**ENTER.**

`=INDEX(D6:G9,1,0)`

- The column number was specified as 0 here. The following formula can also be used to get the entire row:

`=INDEX(D6:G9,1,)`

- If you enter
**=INDEX(D6:G9,1)**and press**ENTER,**you will see the first value in the first row, not the whole row. - To get the first column as a whole, enter the following formula:

`=INDEX(I6:L9,,1)`

**Note:**

- In versions older than
**Microsoft 365**, you must use the Array formula to return a row or column from a range using the**INDEX**Function and press**CTRL + SHIFT + ENTER**to enter the formula. - To return an entire range, assign the range to the reference argument and enter 0 as the column and row number. The formula is:

`=INDEX(D6:G9,0,0)`

### Example 6 – The INDEX Function Can Also Be Used as Cell Reference

Use the following formula:

`=D6:G6`

**INDEX(D6:G9,1,4)** instead of **G6** is used in the above formula:

`=D6:INDEX(D6:G9,1,4)`

- Select a cell for the formula.
- Go to
**Formulas**>>**Formula Auditing**>>**Evaluate Formula**.

- In the Evaluation field, you will see the formula
**=D6:INDEX(D6:G9,1,4)**. - Click
**Evaluate**. - The formula shows
**$D$6:$G$6**. - The
**INDEX**formula returned a cell reference, not a cell value.

## What Are the Common Errors While Using the INDEX Function in Excel?

**The #REF! Error:**

It occurs-

- When your passed row_num argument is higher than the existing row numbers in the range.
- When your passed col_num argument is higher than the existing column numbers in the range.
- When your passed area_num argument is higher than the existing area numbers.

**The #VALUE! Error:**

It occurs when you supply non-numeric values as row_num, col_num, or area_num.

**Download Excel Workbook**

Download the Excel workbook to practice.

**<< Go Back to Excel Functions | Learn Excel**

Have tried the links to download the 1200+ macros examples e-book & 100+ excel functions cheat sheet. However, it keeps asking me to reload or re-register. Please if the file sizes are not too large can you forward to my email address.

Baber,

I have sent you an email with instructions. Please check. I hope the email solves the problem.

Regards

How about adding a link to save the content for later reference?

Ahmed Sheikh

[email protected]

You can do that Ahmed. Thank you.

Regards

I have many data in columns A to E , I want to find all data that corresponding to a specific data from column A . This data from A may be exist three times or more.

I have many data in columns A to E , I want to find all data from column E that corresponding to a specific data from column A. This data from A may be exist three times or more.

Waleed,

Can you upload the working files of your problems? At least a sample file? If possible send an email to this address [email protected]

I am trying to use the index function to display a dollar amount listed in a table in the month that it will be billed for. I have multiple projects and when the formula is dragged down to the next project, the index gets off because the projects have different start dates. Is there a better way to have the index start at the first billing month other than copying the formula from the previous project to the first billing month of the next project?

Hi, GILBERT BECHTOL!

Thank you for your query.

In your appeared problem, I would suggest you use the

MONTHfunction to get individual months from each date record. Then,sortthe order from smallest to largest. As a result, you’ll get the billing months of the project in sequential order and thus you can use theINDEXfunction to achieve your target.If your problem still doesn’t fix, please send us your Excel sheet with clearer feedback on your target in this regard.

Regards,

Tanjim Reza

Dear All:

I am looking for a way (without VBA) to create a dynamic array constant which has the value of {1,1,1;2,2,0;3,0,0} in column 3 and {1,1,1,1;2,2,2,0;3,3,0,0;4,0,0,0} in column 4.. and so on and so forth

This is where I have reached so far:

I was able to figure out that an array formula =CHOOSE(TRANSPOSE(A1:C1),{1,1,1},{2,2,0},{3,0,0}) where A1=1,B1=2,C1=3 gives me the solution and =A1*–(A1:A3<D1) gives me the value of {1,1,1}.. but when I try combining the above two into a single formula as =CHOOSE(TRANSPOSE(A1:C1),A1:C1*–(OFFSET(A1:C1,0,0,1,C1)<D1)).. i am returned the value of {1,2,3;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!}….

I cant seem to figure out how to get the above formula to work or some other way to get the constant {1,1,1;2,2,0;3,0,0}.

Please do help.

Thanks

Akshay

Hi AKSHAY THAKKER! We hope you are well. It’s been 6 years since you posted this query here. We are extremely sorry for being so late in responding. Hope you would have had a solution to your problem somewhere by now. However, we are providing a solution to your question hoping that other readers might find it useful.

For example, we have created a 10×10 array using the following formula.

Look at the following image.

You must do two things before applying the formula.

First, place the numbers in the first row (row 1, i.e. row of A1) serially, and second, place them serially down the first column (column A). You can place them elsewhere, but in that case, you have to change the cell references in the array formula accordingly.

You can create any square array with your desired sequence (1,1,1,1;2,2,2,0;3,3,0,0;4,0,0,0) having any square dimension. However, if you want to change the sequence, you have to change the formula a bit.

Tip:Look into the greater than logic in the formula. You have to make the change here to create other sequences.If there is any query, please let us know. You can also send us your problem at this address: [email protected]