## How to use the INDEX function

*Article last updated on April 17, 2018*

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. |

[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. |

[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. |

[area_num] |
Optional. Selects a range in 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 argument

The first argument in the INDEX function is *array*. What is an array? An array is a range of cells. To demonstrate in greater detail that an array is an array of values you can convert an array or a cell reference to a static group of values by pressing F9.

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

{"Staple",10,10;"Binder",20,6;"Pen",30,1} 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.

### Example 2 - Row_num argument

The second argument in the INDEX function is row_num. It selects the row in an array from which to return a value.

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.

### Example 3 - column_num argument

The column_num argument selects a column from which to return a value. It is optional if you only have values in a single column, like the example above.

Cell range C11:E13 has values separated by commas and semicolons. The cell range C11:E13 is two dimensional.In this example, the value in the third row and the second column is returned.

Read more: Looking up data in a cross reference table

### Example 4 - Area_num argument

Area_num selects a range in *reference* from which to return a value.

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

INDEX((C11:E13,C18:E20),3,1,**2**) has two references to two different cell ranges. The area_num selects from which cell reference to return a value. In this example area_num is two and 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 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. Press Enter. Release all keys.

The example below returns all values from a column.

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

### Example 6 - Index returns a cell reference

**Array formula in cell range C19:D20:**

=C11:INDEX(C11:E13,2,2)

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?

### Download excel *.xlsx file

### Articles with the 'INDEX' Function

The following 33 articles have formulas that contain the INDEX function.

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

### Articles with the 'INDEX' Function

The following 7 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 […]

Two-way lookup using multiple tables [UDF]

This post describes how to lookup values in two dimensions with multiple tables using a User Defined Function. You can […]

### 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

[...] 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. […]