  Get instant live expert help with Excel or Google Sheets “My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

# How to Use the INDEX Function in Excel

The INDEX function returns a value or the reference to a value from within a table or range. It is used to fetch values from tabular data when you have the row and column numbers of the lookup value.

## How to use Excel’s INDEX function

The INDEX function in Excel has two formats, the Array Format (which is the most basic format), and the Range Format. In this tutorial, you will learn how to use both formats of the INDEX function in Excel.

### Syntax

The Array format of the Index function in Excel is used when you want to look up a reference to a cell within a single range. The syntax of the function is:

`=INDEX (array, row_num, [col_num], [area_num])`

### Arguments

array – Required. It is a range of cells, or an array constant. If the array contains only one row or column, the corresponding row_num or col_num argument is optional. If it has more than one row and more than one column, and only row_num or col_num is used, INDEX returns an array of the entire row or column in the array.

row_num – Required. It is the position of the row from which the value is to be fetched. If omitted, col_num is required.

[col_num] – Optional. It is the column position in the reference or array. If omitted, row_num is required.

### Returns

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

### Notes

• If both the row_num and col_num arguments are used, INDEX returns the value in the cell at the intersection of the two.
• If row_num and col_num are both set 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 in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press CTRL+SHIFT+ENTER.

### Syntax

The Range format of the Index function can be used to extract references from ranges that are made up of more than one area. The syntax of the function is:

`INDEX( range, row_num, [col_num], [area_num] )`

### Returns

Extract references from ranges that are made up of more than one area.

### Notes

• After Reference and Area_num have selected a particular range, row_num and col_num select a particular cell: row_num 1 is the first row in the range, col_num 1 is the first column, and so on. The reference returned by INDEX is the intersection of Row_num and Column_num.
• If you set row_num or column_num to 0 (zero), INDEX returns an array of the reference for the entire column or row, respectively.
• row_num, column_num, and area_num must point to a cell within reference; otherwise, INDEX returns the #REF! error. If row_num and col_num are omitted, INDEX returns the area in reference specified by area_num.
• The result of the INDEX function is a reference and is interpreted as such by other formulas. Depending on the formula, the return value of INDEX may be used as a reference or as a value.

## Examples

In the following examples, you will learn how to use the array format of the INDEX function.

### Using the INDEX Function from the Dialog Box

To enter the INDEX function and arguments using the dialog box for the

example image:

1. Click on cell E2, which is the location where the result will be displayed.
2. Click on the Formulas tab of the ribbon menu.
3. Choose Lookup and Reference from the ribbon to open the function drop-down list.
4. Click on INDEX in the list to bring up the function’s dialog box.
5. In the Select Arguments box, select the option that refers to the array array, row_num, col_num. 6. Highlight cells A2 to A8 in the worksheet to enter the array in the dialog box.
7. Click on the row_num line in the dialog box.
8. Insert the number 4 where Excel will fetch the value from.
9. Click on the col_num line in the dialog box.
10. Enter the number 2 on this line to return the value from column 2 which is Years of Experience.
11. Click OK to complete the function and close the dialog box. 12. The number 7 appears in cell E2 since the employee in row four, Sven Atkins has 7 years of experience. When you click on cell E2 the complete function `=INDEX(A2`:B7,4,2`) `appears in the formula bar above the worksheet.

### Index Function Excel (Array Format)

In the following example, assign the formula `=INDEX(A2:B7,4,2)` to cell E2. The Index function returns a reference to row 4 of the range A2:B7, which is cell B5. This has the value 7. ### Index Function Excel (Range Format)

In the following example, to find the Production hour of the month Jun, assign the formula `=INDEX((C2:D5,D7:E10,F2:G5),3,2,2)` to cell G8. The Index function returns a reference to row 2 and column 2 of the 3rd area in the supplied range. This is cell G8, which evaluates to the value 142.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.

### Did this post not answer your question? Get a solution from connecting with the expert. Another blog reader asked this question today on Excelchat:
Solution examples I need help on index and match where the return is not correct
Solved by B. B. in 20 mins I have a database extract that is showing the same clients multiple times in the list. How can I write a formula in excel to look at the column with the client names and return only unique records on another sheet? I want to run this over several files so wanting to avoid using pivot tables which is how I would normal do this.
Solved by X. J. in 20 mins Hi, I have created a drop down list and in another cell I have used an IF function which is dependent on the drop down list. However, when i change the selected option from the drop down list, the IF function cell does not update accordingly
Solved by C. C. in 17 mins I work shift work as do about 100 other guys. All I want to do is type in the days date (Or just open the sheet and it will have the days date) and it has on there who is working that day/night. Pretty simple I would think...And if I change the date to, let's say, a month down the road, it pulls in the guys working that day... That all...Thx
Solved by O. E. in 20 mins index and match with duplicates. I need to use another column as a reference, so my return value has two match the value of two things for it to return
Solved by E. H. in 60 mins ## Subscribe to Excelchat.co Another blog reader asked this question today on Excelchat: