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.
- Array Format
- 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
- Row and Column Number set to Zero (0) – Returns Entire array or range or table
- Column and Row Numbers set to specific numbers – Returns value from the intersection cell of the row and column in the array.
- Row Number set to a specific value and column number set to Zero – Returns entire row from the array
- 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.
Specific Value set for Row and Column
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
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
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.
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
Pingback: How to use MATCH Function in Excel - Syntax and Examples - Excel Bits