How to use the INDEX function
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
array or cell reference | Required. The cell range you want to get a value from. You can also use an array. |
[row_num] | Optional. The relative row number of a specific value you want to get. If omitted the INDEX function returns all values if you enter it as an array formula. Update! The 365 subscription version of Excel returns all values without needing to enter the formulas an array formula. |
[column_num] | Optional. The relative column number of a specific value you want to get. If omitted the INDEX function returns all values if you enter it as an array formula. Update! The 365 subscription version of Excel returns all values without needing to enter the formulas an array formula. |
[area_num] | Optional. A number representing the relative position of one of the ranges in the first argument. |
Table of Contents
- Overview
- Example 1 - array argument
- Example 2 - row_num
- Example 3 - column_num
- Example 4 - area_num
- Example 5 - Return a range of values
- 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.
- Select cell range D3:D8.
- Type the formula =INDEX(C9:C14,0)
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
- 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.
- Select cell range C19:E19.
- Type =INDEX(C11:E13,3,0) in formula bar.
- Press and hold CTRL + SHIFT simultaeously.
- Press Enter.
- 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?
Article with the 'INDEX' Function
The following article has a formula that contains the INDEX function.
Rearrange cells in a cell range to vertically distributed values
The formula in cell B8 uses a named range to calculate the row and column needed to extract the correct […]
Articles with the 'INDEX' Function
The following 6 articles have formulas that contain the INDEX function.
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
How to perform a two-dimensional lookup
Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]
The table of contents below lets you quickly navigate to the formula you are looking for. The excel 2016 formula […]
Have you ever tried to build a formula to calculate discounts depending on price? The VLOOKUP function is much easier to […]
Functions in 'Lookup and reference'
The INDEX function function is one of many functions in the 'Lookup and reference' category.
How to use the ADDRESS function
The ADDRESS function returns the address of a specific cell, you need to provide a row and column number. Formula […]
The AREAS function returns the number of cell ranges and single cells in a reference. Formula in cell B3: =AREAS(E3:E4) […]
How to use the CHOOSE function
The CHOOSE function allows you to retrieve a value based on a number, cell range C3:C4 in the picture above […]
How to use the COLUMN function
The COLUMN function returns the column number of the top left cell of a cell reference. If the argument is not […]
How to use the COLUMNS function
The COLUMNS function allows you to calculate the number of columns in a cell range. The example above shows that cell […]
How to use the FORMULATEXT function
The FORMULATEXT function returns a formula as a text string. Formula in cell C3: =FORMULATEXT(B3) Excel Function Syntax FORMULATEXT(reference) Arguments […]
How to use the HLOOKUP function
The HLOOKUP function lets you search the top row in a data range for a value and return another value […]
How to use the HYPERLINK function
The HYPERLINK function allows you to build a link in a cell pointing to something else like a file, workbook, cell, […]
Gets a value in a specific cell range based on a row and column number.
How to use the INDIRECT function
The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference. […]
How to use the LOOKUP function
Finds a value in a sorted cell range and returns a value on the same row.
Identify the position of a value in an array.
How to use the OFFSET function
The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]
The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]
The ROWS function allows you to calculate the number of rows in a cell range. The example above shows that cell […]
How to use the TRANSPOSE function
The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]
How to use the VLOOKUP function
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row […]
16 Responses to “How to use the INDEX function”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
[...] INDEX function returns a value of the cell at the intersection of a particular row and column, in a given range. [...]
[...] INDEX(array,row_num,[column_num]) Returns a value or reference of the cell at the intersection of a particular row and column, in a given range [...]
[...] INDEX(array,row_num,[column_num]) Returns a value or reference of the cell at the intersection of a particular row and column, in a given range [...]
[…] INDEX(array,row_num,[column_num]) Returns a value or reference of the cell at the intersection of a particular row and column, in a given range […]
[…] INDEX(array,row_num,[column_num]) Returns a value or reference of the cell at the intersection of a particular row and column, in a given range […]
[…] INDEX(array,row_num,[column_num]) Returns a value or reference of the cell at the intersection of a particular row and column, in a given range […]
Hi there
I have a list of serial numbers in column A. I need to bath them in batches of 20 and hence custom naming the batches with the number increment increasing.
Eg Column A Column B
987654 vodacom_03022015_75062
987655 vodacom_03022015_75062
so column A 2o rows will have same batch name and number
next 20 will increase number in column b by 1...
PLEase help
If you wish for to get a good deal from this post
then you have to apply these strategies to
your won website.
SIR MY NAME IS JAGBIR SINGH.
I AM JUST CONFUSE FOR TAKING INDEX FORMULA INSTEAD OF V LOOKUP
BY VLOOKUP WE CAN TAKE DATA OF ANOTHER SHEET INTO ONE SHEET.
BUT BY INDEX FORMULA I AM UNABLE TO DO THAT.
PLEASE SUGGEST ME HOW ITS WORK FOR TWO SHEET.
JAGBIR SINGH,
This formula returns a value from sheet2:
INDEX(Sheet2!$A$2:$A$10, MATCH($A$2,Sheet2!$B$2:$B$10,0))
hi sir i have confusion on some matter to sortin data from list of given names
hello sir,
i have a column with various data and i want to show it in 1 cell.
https://gyazo.com/a1b23e2530007a4e9d25ea4b711d8bbb
can you help me how to do it
thanks
[…] and returns cell ref A22. Read more about INDEX function. […]
[…] INDEX(array,row_num,[column_num]) Returns a value or reference of the cell at the intersection of a particular row and column, in a given range […]
[…] INDEX(array,row_num,[column_num]) Returns a value or reference of the cell at the intersection of a particular row and column, in a given range […]
[…] INDEX(array,row_num,[column_num]) Returns a value or reference of the cell at the intersection of a particular row and column, in a given range […]