Exceljet

Quick, clean, and to the point

Excel INDEX Function

Excel INDEX function
Summary 
The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.
Purpose 
Get a value in a list or table based on location
Return value 
The value at a given location.
Syntax 
=INDEX (array, row_num, [col_num], [area_num])
Arguments 
  • array - A range of cells, or an array constant.
  • row_num - The row position in the reference or array.
  • col_num - [optional] The column position in the reference or array.
  • area_num - [optional] The range in reference that should be used.
Usage notes 

Use the INDEX function to get a value from a list or table based on its location.

Examples

The formulas below show how INDEX can be used to get a value:

=INDEX(A1:B5,2,2) // returns value in B2
=INDEX(A1:B5,3,1) // returns value in A3

In the examples above, the location is "hardcoded". Typically, the MATCH function is used to find the location, which is then fed into INDEX. This article explains how INDEX and MATCH can be used together with many examples:

How to use INDEX and MATCH

It's important to note that INDEX function returns a reference as a result. For example, in the following formula, INDEX returns A2:

=INDEX(A1:A5,2) // returns A2

In a typical formula, you'll see the value in cell A2 as the result, so it's not obvious that INDEX is returning a reference. However, this is a useful feature in formulas like this one, which uses INDEX to dynamically return the sum of an entire column.

The INDEX function has two forms: array and reference. Both forms have the same behavior – INDEX returns a reference in a array based on a given row and column location. The difference is that the reference form of INDEX allows more than one array, along with an optional argument to select which array should be used. Most formulas use the array form of INDEX, but both forms are discussed below. 

Array form

In the array form of INDEX, the first parameter is array, which is supplied as a range of cells or an array constant. The syntax for the array form of INDEX is:

INDEX(array,row_num,[col_num])
  • If both row_num and col_num are supplied, INDEX returns the value in the cell at the intersection of row_num and col_num.
  • If row_num is set to zero, INDEX returns an array of values for an entire column. To use these array values, you can enter the INDEX function as an array formula in horizontal range, or feed the array into another function.
  • If col_num is set to zero, INDEX returns an array of values for an entire row. To use these array values, you can enter the INDEX function as an array formula in vertical range, or feed the array into another function.

Reference form

In the reference form of INDEX, the first parameter is reference to one or more ranges, and a fourth optional argument, area_num, is provided to select the appropriate range. The syntax for the reference form of INDEX is:

INDEX(reference,row_num,[col_num],[area_num])

Just like the array form of INDEX, the reference form of INDEX returns the reference of the cell at the intersection row_num and col_num. The difference is that the reference argument contains more than one range, and area_num selects which range should be used. The area_num is argument is supplied as a number that acts like a numeric index. The first array inside reference is 1, the second array is 2, and so on.

For example, in the formula below, area_num is supplied as 2, which refers to the range A7:C10:

=INDEX((A1:C5,A7:C10),1,3,2)

In the above formula, INDEX will return the value at row 1 and column 3 of A7:C10.

  • Multiple ranges in reference are separated by commas and enclosed in parentheses.
  • All ranges must on one sheet or INDEX will return a #VALUE error. Use the CHOOSE function as a workaround.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.