Return the value or reference of specified cell(s)

Array form: =INDEX(array, row_num, [column_num])

Reference form: =INDEX(reference, row_num, [column_num], [area_num])

---

---

The array form of INDEX defines 'where to search', 'the row number to return' and 'the column number to return'.

The reference form of INDEX defines 'where to search', 'the row number to return', 'the column number to return' and 'the range from which to return the intersection of row and column'.

For example, the array formula =INDEX(B3:F12,3,4) will return the value from the range B3:F12 that is 3 rows down and 4 columns across.

The reference formula =INDEX(D3:E12,4,0,1) will return the array of values in the range D3:E12 that are 4 rows down.

Dynamic row and column references can be created by applying the MATCH function. The array formula =INDEX(B3:F12,MATCH("Bang*",B3:B12,0),2) will return the value from the range B3:F12 that is in the row that exactly matches the wildcard value of "Bang*" (e.g. Bangladesh) and 2 columns across.

---

INDEX: array form.

In the examples below, the INDEX function is used to match population data by year to specific countries and perform calculations on population data. Note the use of the MATCH function and wildcards.

---

INDEX: reference form.

In the examples below, the INDEX function is used to return arrays or the sum of population data for one or many countries.

---

Syntax: array form

=INDEX(array, row_num, [column_num])

The INDEX function consists of at least 2 arguments:

1. array. Required. A range of cells or an array constant.
2. row_num. Required. The row in the array from which to return a value. If row_num is omitted, column_number is required.
3. column_num. Optional. The column in the array from which to return a value. If column_num is omitted, row_number is required.

---

Syntax: reference form

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

The INDEX function consists of at least 2 arguments:

1. reference. Required. A reference to one or more range of cells. Non-adjacent ranges should be enclosed in parantheses.
2. row_num. Required. The row in the reference from which to return a value.
3. column_num. Optional. The column in the reference from which to return a value.
4. area_num. Optional. The range in reference from which to return the intersection of row_num and column_num.

---

Try it now!

• Enter your formulas into the grey cells.

---

Scope

INDEX: array form defines 'where to search', 'the row number to return' and 'the column number to return'.

INDEX: reference form defines 'where to search', 'the row number to return', 'the column number to return' and 'the range from which to return the intersection of row and column'.

Array form:	=INDEX(B3:F12,3,4) || result is a value from the range B3:F12 that is 3 rows down and 4 columns across.
Array form with MATCH:	=INDEX(B3:F12,MATCH("Bang*",B3:B12,0),2) || result is a value from the range B3:F12 that is an exact match for the wilcard "Bang*" and is 2 columns across.
Reference form:	=INDEX(D3:E12,4,0,1) || result is all values from the range D3:E12 that are 4 rows down.
Reference form with SUM:	=SUM(INDEX(B3:F12,0,5,1)) || result is the sum of values from the 5th column across in the range B3:F12.
Reference form with SUM:	=SUM(D8:INDEX(B8:F12,0,3,1)) || result is the sum of values from the range D8:(the 3rd column across in the range B3:F12).

Caveats

There are 2 ways to use the INDEX function: array form and reference form.

• Array form returns the value of a specified cell or array of cells, identified by row and column number indexes.
• Reference form returns a reference to specified cells.

Array form: =INDEX(array, row_num, [column_num]).

Dynamic array formulas can be entered into Excel 365 using ENTER but require CTRL + SHIFT + ENTER in prior versions of Excel.
• array
  • Array contains only one row or column: corresponding row_num or column_num argument is optional.
  • Array contains more than one row and more than one column, and only row_num or column_num is used: an array of the entire row or column is returned.
• row_num
  • Required, unless column_num is present.
  • If set to 0 (zero), INDEX returns the array of values for the entire row.
  • Must point to a cell within array, otherwise a #REF! error is returned.
  • If row_num is omitted, column_num is required.
• column_num
  • Optional.
  • If set to 0 (zero), INDEX returns the array of values for the entire column.
  • Must point to a cell within array, otherwise a #REF! error is returned.
  • If column_num is omitted, row_num is required.
  • If both row_num and column_num, INDEX returns the value in the cell at the intersection of row_num and column_num.

Reference form: =INDEX(reference, row_num, [column_num], [area_num]).

• reference
  • Enclose references to non-adjacent ranges in parentheses.
• row_num
  • Required, unless each area in reference contains only one row or column. For example, for a single column reference, use =INDEX(reference,row_num).
• column_num
  • Optional, unless each area in reference contains only one row or column. For example, for a single row reference, use =INDEX(reference,,column_num).
• area_num
  • Optional. Selects a range in reference from which to return the intersection of row_num and column_num. The first area selected or entered is numbered 1, the second is 2 etc. If area_num is omitted, INDEX defaults to area 1.
  • The areas listed must all be located on one sheet, otherwise the #VALUE! error is returned.

---

Common issues

• #REF!: either row_num or column_num do not point to a cell within the array.
• #VALUE!: areas listed in area_num are located on different sheets.

---

Related Functions

• MATCH to return the relative position of a specified item in a range.

---

Functions Category

• Lookup & Reference

 

---