get-digital-help.com

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

Excel Function Syntax

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

Arguments

Table of Contents

1. Overview
2. Example 1 - array argument
3. Example 2 - row_num
4. Example 3 - column_num
5. Example 4 - area_num
6. Example 5 - Return a range of values
7. Example 6 - Index returns a cell reference

Example 1 - array or cell range argument

The first argument in the INDEX function is array or a cell reference to a cell range. What is an array? An array is a range of values hardcoded into the formula.

To demonstrate in greater detail what an array is you can convert an array or a cell reference to a group of constants by selecting the cell reference and then press F9 to convert the cell reference to values, see the animated image above.

When you convert a cell range to constants Excel automatically creates double quotes around text values, however, note that numbers are not changed.

B6:D8 becomes {"Staple",10,10;"Binder",20,6;"Pen",30,1} and each value is separated by a delimiting character. Comma (,) is used to separate columns and semicolon (;) to separate rows.

The English language version of excel uses commas and semicolons, other language versions of excel may use other characters. You can change this in the Regional settings in Windows.

Here is an example of an array used in an INDEX function:

The greatest disadvantage of using an array is that you need to edit the formula if you need to change one of the values in the array, contrary to a cell reference.

Here is an example of a cell reference being used in an INDEX function:

You don't need to edit this formula if one of the values in cell range B6:D8 is changed, the formula is using the new value automatically.

Remember that relative cell references (B6:D8) changes when you copy the cell and paste to cells below. Absolute cell references ($B$6:$D$8) do not change when the cell is copied to cells below.

Read more about converting cell references or formulas: Replace a formula with its result

Example 2 - Row_num argument

The second argument in the INDEX function is the row_num. It allows you to choose the row in an array or cell range, from which to return a value.

If you use an array or cell range with values distributed in one column only there is no need to use the second optional argument which specifies the column, there is only one column to use. Here is an example of an array contining values in a single column, no comma as a delimiting value in this array which would have indicated that there would have been multiple columns.

The following formula uses a cell reference instead of hardcoded values:

Cell range C9:C14 has values separated by a semicolon. The cell range is one dimensional. In this example, the value from the second row will be returned, see image above.

It is also possible to return an array of values if you omit or use a zero as row_num argument:

Both these formulas return an array of values. To be able to display all values you need to enter the formula as an array formula in a cell range that has the same number of cells as the cell range or values in the array.

1. Select cell range D3:D8.
2. Type the formula =INDEX(C9:C14,0)
3. Press and hold CTRL + SHIFT simultaneously.
4. Press Enter once.
5. Release all keys.

The formula in the formula bar changes to {=INDEX(C9:C14,0)}, do not add these curly brackets yourself, they appear automatically. See image above.

Update 1/22/2020!

Excel users owning Excel 365 subscription version now have the option to not enter the formula as an array formula but as a regular formula. They are called dynamic arrays and behaves differently than array formulas. Array formulas can still be used in order to be compatible with earlier Excel versions, however, Microsoft suggests that you should from now on use dynamic arrays instead of array formulas.

The formula is entered as a regular formula and extends automatically if the cells needed below are empty, this is called spilling by Microsoft. The remaining cells show a greyed out formula in the formula bar, only the first cell contains a formula in black.

The blue border around the cell range indicates that the cell range contains a spilled formula and disappears when you click a cell outside the range.

Example 3 - column_num argument

The column_num argument allows you to choose a column from which to return a value. This argument is optional, for example, if you only have values in a single column.

The cell range C11:E13 is two-dimensional meaning there are multiple rows and columns. In this example, the value in the third row and the second column is returned.

I have greyed out the row and column numbers in the image above, this makes it easier to see that value 30 is where row 3 and column 2 interesects.

The following formula has an array containing constants.

{"Staple", 10, 10; "Binder", 20, 6; "Pen", 30, 1} has values separated by commas and semicolons meaning commas separate values between columns and semicolons separate values between rows.

Read more: Looking up data in a cross reference table

Example 4 - Area_num argument

The INDEX function lets you have multiple cell references in the first argument, the area_num argument allows you to pick a cell range in the reference argument.

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

The following formula has two references pointing to two different cell ranges.

The area_num selects from which cell reference to return a value. In this example area_num is two therefore the second cell reference is used. The item in the third row and first column is returned.

Example 5 - Return a range of values

The INDEX function is also capable of returning an array from a column, row, and both columns and rows. The following formula demonstrates how to extract all values from row three:

The formula in cell C19:E19 is an array formula.

1. Select cell range C19:E19.
2. Type =INDEX(C11:E13,3,0) in formula bar.
3. Press and hold CTRL + SHIFT simultaeously.
4. Press Enter.
5. Release all keys.

The example above demonstrates an array formula that returns all values from column 1 from cell range C11:E13.

The example below returns all values from a two-dimensional cell range.

The following array formula returns all values on all rows and columns from a cell range.

Example 6 - INDEX function returns a cell reference

The INDEX function can also be used to create a cell reference, for example, a dynamic range created by a formula in a named range.

Array formula in cell range C19:D20:

INDEX(C11:E13,2,2) returns cell reference D12

C11:D12 returns {"Staple",10;"Binder",20}

Final note

There are some magic things you can do with the array argument. See this post: No more array formulas?

Excel Function Reference

This extensive function reference list provides detailed information including syntax, arguments, return values, and examples for many of the functions used in Excel formulas. Click on headers to sort table.