Summary
The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function in excel is often used together with INDEX to provide row and column numbers.
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.
Version
Free excel tutorial online
Usage notes
The INDEX function returns the value at a given location in a range or array. INDEX is a powerful and versatile function. You can use INDEX to retrieve individual values, or entire rows and columns. INDEX is frequently used together with the excel index function . In this scenario, the MATCH function locates and feeds a position to the INDEX function, and INDEX returns the value at that position.
In the most common usage, INDEX takes three arguments: array, row_num, and col_num. Array is the range or array from which to retrieve values. Row_num is the row number from which to retrieve a value, and col_num is the column number at which to retrieve a value. Col_num is optional and not needed when array is one-dimensional.
In the example shown above, the goal is to get the diameter of the planet Jupiter. Because Jupiter is the fifth planet in the list, and Diameter is the third column, the formula in G7 is:
=INDEX(B5:E13,5,3) // diameter of Jupiter
The formula above is of limited value because the row number and column number have been hard-coded. Typically, the MATCH function would be used inside INDEX to provide these numbers. For a detailed explanation with many examples, see index function excel.
Basic usage
INDEX gets a value at a given location in a range of cells based on numeric position. When the range is one-dimensional, you only need to supply a row number. When the range is two-dimensional, you'll need to supply both the row and column number. For example, to get the third item from the one-dimensional range A1:A5:
=INDEX(A1:A5,3) // returns value in A3
The formulas below show how INDEX can be used to get a value from a two-dimensional range:
=INDEX(A1:B5,2,2) // returns value in B2=INDEX(A1:B5,3,1) // returns value in A3
INDEX and MATCH
In the examples above, the position is "hardcoded". Typically, the MATCH function is used to find positions for INDEX. For example, in the screen below, the MATCH function is used to locate "Mars" (G6) in row 3 and feed that position to INDEX. The formula in G7 is: index formula in excel
=INDEX(B5:E13,MATCH(G6,B5:B13,0),3)
MATCH provides the row number (4) to INDEX. The column number is still hardcoded as 3.
INDEX and MATCH with horizontal table
In the screen below, the table above has been transposed horizontally. The MATCH function returns the column number (4) and the row number is hardcoded as 2. The formula in C10 is:
=INDEX(C4:K6,2,MATCH(C9,C4:K4,0))
For a detailed explanation with many examples, see: excel index formula