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

Formula in cell C9:

This formula returns a value from row 2 and column 3 based on cell range B3:E7, note these are relative positions.

The image above shows the relative row and column numbers, row 2 and column 3 are highlighted. The intersection of those two is the value the INDEX function returns.

## Video

**Table of Contents**

- Function Syntax
- Arguments
- How to use an array in INDEX function
- How to use the
*row_num*argument - Return an array of values - INDEX function
- How to use the [column_num] argument
- How to use the [area_num] argument - INDEX function
- How to return the entire row using the INDEX function
- How to build a dynamic cell reference using the INDEX function
- Get Excel file

## 1. Excel Function Syntax

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

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

## 3. How to use an array in INDEX function

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

## 4. How to use the *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 containing 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.

## 5. Return an array of values - INDEX function

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 the 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 press with left mouse button on a cell outside the range.

## 6. How to use the [*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

## 7. How to use the [*area_num*] argument - INDEX function

The INDEX function lets you have multiple cell references in the first argument, the a*rea_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.

**2**)

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 the first column is returned.

## 8. How to return the entire row using the INDEX function

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.

### 8.1 How to return a column - INDEX function

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

### 8.2 How to return a two-dimensional cell range - INDEX function

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.

## 9. How to build a dynamic cell reference using the INDEX function

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?

## 10. Excel file

### 'INDEX' Function examples

The following 222 articles have formulas containing the INDEX function.

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

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

Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

This article demonstrates a formula that distributes given text strings randomly across records in any given day meaning they may […]

Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]

Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]

Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]

Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]

Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]

Question: List of data and blank cells in a column which will be added from day to day. There are […]

The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]

In this example we are going to use two lists with identical columns, shown in the image above. It is […]

This article demonstrates a formula that extracts values that exist only in one column out of two columns. There are […]

The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]

How would you figure out an unique list where the sum of in one column doesn't match the other column? […]

This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]

Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]

The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

Question: How do I count how many times a word exists in a range of cells? It does not have […]

This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]

The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]

This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]

The image above shows a formula in cell C2 that searches for a value based on two conditions specified in […]

Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]

This article describes how to create a random playlist based on a given number of teams using an array formula. […]

Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]

The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]

Table of Contents Delete blanks and errors in a list How to find errors in a worksheet 1. Delete blanks […]

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]

This stock chart built in Excel allows you to change the date range and the chart is instantly updated. What's […]

Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]

The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range […]

Lookup with criteria and return records.

Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]

This article describes how to filter duplicate rows with the use of a formula. It is, in fact, an array […]

The following array formula in cell B11 extracts duplicates from cell range B3:E8, only one instance of each duplicate is […]

I this article I will show you how to get numerical values from a cell range manually and using an […]

Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array […]

Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Table of Contents Extract shared values between two columns Extract shared values between two columns - Excel 365 Extract shared […]

This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]

The picture above shows an array formula in cell B9:C13 that extracts unique distinct records from two tables in cell […]

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]

Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]

Question: I want a unique distinct list to be created from a column where an adjacent column has text cell […]

Filtering unique distinct text values and sort them based on the sum of adjacent values can easily be done by […]

The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]

Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]

This article demonstrates formulas that extract duplicates based on three conditions. Table of Contents Filter duplicate values using criteria How […]

The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]

Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]

Table of Contents Filter unique distinct values based on a date range How to enter an array formula Filter unique […]

EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]

Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]

A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]

The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]

This article explains how to extract values that exist in three different columns, they must occur in each of the […]

Table of Contents Find closest value How to enter an array formula Find closest value - Excel 365 Find closest […]

Bill Truax asks: Hello Oscar, I am building a spreadsheet for tracking calls for my local fire department. I have a […]

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

The image above shows a formula in cell D3 that extracts the latest date in cell range B3:B15. =MAX(B3:B15) The MAX […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]

In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the […]

This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.

The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based […]

Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]

I discovered this chart from Google Public policy blog and it got me thinking if I could do the same […]

This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]

This article demonstrates a worksheet that highlights lookups across relational tables. I am using Excel defined Tables, if you add […]

This article demonstrates how to create a chart that animates the columns when filtering chart data. The columns change incrementally […]

A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]

JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]

This article describes how to create a map in Excel, the map is an x y scatter chart with an […]

Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]

This article demonstrates a formula and a VBA macro that returns every n-th row from a given cell range. The […]

### Functions in 'Lookup and reference' category

The INDEX function function is one of many functions in the 'Lookup and reference' category.

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

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

In Example 4 - Area_num argument, the first range (C11:E13) does not do any thing, why we need it there?

=INDEX(C18:E20,3,1) does the same and simple.

So, what's the purpose of =INDEX((C11:E13,C18:E20),3,1,2)?

Thank you!