Home » INDEX Function in Excel – How to use with Examples

INDEX Function in Excel – How to use with Examples

How to use Index Function in Excel - excelbits.com

The Index function in Excel is one of the most used functions in excel. It is categorized under Lookup and Reference functions. In this article we will see and learn How to use and Syntax along with few examples.

What is Index Function in Excel

The excel INDEX function returns a value or the reference to a value from within a table or range. You can use the index function to retrieve or return an individual value, row or a column. In simple terms, Index function returns the value of a cell at the intersection of the specific row and column numbers.

Index is frequently used together with MATCH function as an alternative to the VLookup function.

Suggested Reading : Using VLookup in excel with examples

Syntax of Index Function

There are 2 formats for Index in excel.

  1. Array Format
  2. Reference Format

Both the formats work in the similar way with a minor difference. The Array format limits to only one array where as the reference format allows more than one array along with an optional argument. This optional form helps to select the array that you want to use. Let us see both the formats and how to use them.

Array Format of Index Function

Array Format returns the value from the intersection of a row and column number in a table or array. You can use this format if you have the first argument as constant.

The syntax of Array Format is as follows.

=INDEX(array,row_num,Column_num)

Array – The specified array or range of cells or named ranges or a table of data. (Required or Mandatory Argument)

Row_Num – The position of the row in the array or range or named range or table you want to look up. Remember this position is the row position in the table or array or range but not the worksheet row number. (Required or Mandatory Argument)

Column_Num – The position of the column in the array or range or named range or table you want to look up. Remember this position is the column position in the table or array or range but not the worksheet row number.

See below the return values with different row and column number combinations

  1. Row and Column Number set to Zero (0) – Returns Entire array or range or table
  2. Column and Row Numbers set to specific numbers – Returns value from the intersection cell of the row and column in the array.
  3. Row Number set to a specific value and column number set to Zero – Returns entire row from the array
  4. Column Number set to a specific value and Row number set to Zero- Returns entire column from the array.

Suggested Reading

Examples for Array Format

Let us see few examples of using Array Format of Index for different scenarios. See the below image which shows the sales data with a stock description and code.

Sample Data

Specific Value set for Row and Column

Index with row and column number - Array Format

In the above Image Row Value is set to 2 and Column Value is set to 3. However, Array is from A2 to F18 Cells. The value in Row 2 and Column 3 which is the value in C3 – “White Metal Lantern” is the return value.

Row Set to Zero and Specific Value set for Column

Row set to zero in Index

In the above Image Row Value is set to 0 and Column Value is set to 2. Array is from A1 to F18 Cells. All the values in entire Column 2 is the return value. As a result you see all values in column B as return value.

Column Set to Zero and Specific Value set for Row

Column set to zero in Index

In the above Image Row Value is set to 4 and Column Value is set to 0. Array is from A1 to F12 Cells. All the values in entire row 4 will be the return value. As a result you see all values in row 4 as return value.

Reference Format of Index Function

In Reference Format the first argument is a reference, where you can choose multiple ranges. The Reference format also comes with an optional parameter Area_Num which enables to choose the range in case if multiple ranges are given in the first argument.

The Syntax for Reference format is

=INDEX(Reference,row_num,Column_num,Area_num)

Reference – The specified range or ranges of cells.

Row_Num – The position of the row in the array or range or named range or table you want to look up. Remember this position is the row position in the table or array or range but not the worksheet row number. (Required or Mandatory Argument)

Column_Num – The position of the column in the array or range or named range or table you want to look up. Remember this position is the column position in the table or array or range but not the worksheet row number.

Area_Num – The range from which return value should be.

Example for Reference Format

We will use the same example which we used for Array format.

index using reference format

In the above image, Column H has index formula, everything remains the same in the formula. However the only change is the last argument which is area_num.

In the first formula, the last argument – area number is 1 which refers to the range A1:F5 , hence index returns the value which is in 3rd row 2nd column in A1:F5 Range. i.e. B3=71053

Whereas, in the second formula, the the last argument – area number is 2, which refers to the range A7:F11. Hence index returns the value which is in 3rd row 2nd column in A7:F11 Range. i.e. B9=22633

Limitations of Index

  • When using reference format of Index function
    • Multiple Ranges should be separated by commas and should be enclosed in parentheses. (see the above image)
    • All the ranges should be in the same sheet if not Index function returns #Value error.
  • Row, Column and Area Numbers should be Numeric, if not the function returns #value error.
  • Row Column Numbers should not exceed the number of rows or columns in the original data. Area Number should not exceed the number of areas or ranges given in the argument.

Conclusion

Index is one of the most flexible and useful function in excel. More usage of Index is in advanced formulas. As mentioned earlier, Index and Match combination is used as an alternative to vlookup to do a two way look up or a dynamic look up. For analysts Index is a must to know function considering the flexibility it offers.

Do you use Index Function ? Share the most common scenarios that you use the Index function in comment box below.

Do not forget to share this post

Index

1 thought on “INDEX Function in Excel – How to use with Examples”

  1. Pingback: How to use MATCH Function in Excel - Syntax and Examples - Excel Bits

Leave a Reply

Scroll to Top
Scroll to Top