How to Use the INDEX Function in Excel – 6 Examples

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:

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:

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.

Or,

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:

Or,

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.

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.

Or,

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:

• Press ENTER.

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.

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

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.

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

• 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:

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:

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

Use the following formula:

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

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