Introduction

INDEX function in excel is a lookup or reference function. It is used to return reference or value from a table or array to another location. It uses both row and column position to retrieve the value. It can return a row, column or a single cell. It is often used in combination with MATCH function, MATCH acts as a feeder for INDEX. It has two forms- Array and Reference. 

Array form

In the Array form, first argument in syntax is an array, which is in form of range of cells or an array constant.

SYNTAX

INDEX(array, row_num, col_num)

Arguments

Array- It is in form of a range of cells or an array constant

Row_num- Row number in array from which value is to be returned.

Column_num- Column number in array from which value is to be returned.

KEYNOTES

• If both row and column number are entered than function returns the value which is present on the position of intersection of that row and column.
• If in row_num argument 0 is used than it retrieves value of the complete row.
• If in column_num argument 0 is entered than it retrieves value of the complete column.

 Reference form

In the Reference form, first argument in syntax is a reference, which is in form of reference to a range or number of ranges.

SYNTAX

INDEX(reference, row_num, col_num,area_num)

Arguments:-

• Reference- Refer to a range or number of ranges.
• Row_num- Row number in array from which value is to be returned.
• Column_num- Column number in array from which value is to be returned.
• Area_num- It determines which range will be used if multiple ranges are added.

KEYNOTES

• Reference form supplies the reference of cell which is at an inter section of row_num, column_num.
• For multiple ranges area_num is used to determine which range to be applied.
• Area_sum is in form of a number.

Examples

In the following example, a list of products with their numbers and price are shown,they are than indexed into a separate table.