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

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

index function

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

Index function 1

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.

Index function 2

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 function 3

INDEX(referencerow_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

Index function 4

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.

Index function 5

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

Index function 6

Example 6 - Index returns a cell reference

Index function 7

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

INDEX function.xlsx