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.
In the Array form, first argument in syntax is an array, which is in form of range of cells or an array constant.
INDEX(array, row_num, col_num)
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.
- 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.
In the Reference form, first argument in syntax is a reference, which is in form of reference to a range or number of ranges.
INDEX(reference, row_num, col_num,area_num)
- 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.
- 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.
In the following example, a list of products with their numbers and price are shown,they are than indexed into a separate table.