# How to use Excel INDEX function

## 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.