## Index function explained

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

### Overview

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

In other words, the INDEX function returns a value or values from a cell range. You decide from which row and column. It can also return 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

### 13 Responses to “Index function explained”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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