Excel INDEX Function

The Excel INDEX function returns a value that is referenced from a specified range

Example: Excel INDEX Function

Excel INDEX Function

METHOD 1. Excel INDEX Function using hardcoded values

EXCEL

=INDEX(B5:C11,4,2)
Result in cell E14 ($5.40) - returns the value in the forth row and second column relative to the specified range.

=INDEX((B5:C8,B9:C11),3,2,2)
Result in cell E15 ($7.40) - returns the value in the third row and second column relative to the second range specified in the formula.

=INDEX((B5:D8,B9:D11),2,3,2)
Result in cell E16 ($3.00) - returns the value in the second row and third column relative to the second range specified in the formula.

=INDEX((B5:C8,B9:C11,Index_Defined_Name),2,3,3)
Result in cell E17 ($2.10) - returns the value in the second row and third column relative to the range specified by the defined name named Index_Defined_Name which comprises a B5:D11 range.

METHOD 2. Excel INDEX Function using links

EXCEL

=INDEX(B5:C11,B14,C14)
Result in cell E14 ($5.40) - returns the value in the forth row and second column relative to the specified range.

=INDEX((B5:C8,B9:C11),B15,C15,D15)
Result in cell E15 ($7.40) - returns the value in the third row and second column relative to the second range specified in the formula.

=INDEX((B5:D8,B9:D11),B16,C16,D16)
Result in cell E16 ($3.00) - returns the value in the second row and third column relative to the second range specified in the formula.

=INDEX((B5:C8,B9:C11,Index_Defined_Name),B17,C17,D17)
Result in cell E17 ($2.10) - returns the value in the second row and third column relative to the range specified by the defined name named Index_Defined_Name which comprises a B5:D11 range.

METHOD 3. Excel INDEX function using the Excel built-in function library with hardcoded values

EXCEL

Formulas tab > Function Library group > Lookup & Reference > INDEX > populate the input box

=INDEX(B5:C11,4,2)
Note: in this example we are populating the Array, Row_num and Column_num INDEX function arguments. 		Built-in Excel INDEX Function using hardcoded values - return the value in the forth row second column from the range

=INDEX((B5:C8,B9:C11),3,2,2)
Note: in this example we are only populating all of the INDEX function arguments. 		Built-in Excel INDEX Function using hardcoded values - return the value in the third row second column of the second range

METHOD 4. Excel INDEX function using the Excel built-in function library with links

EXCEL

Formulas tab > Function Library group > Lookup & Reference > INDEX > populate the input boxes

=INDEX(B5:C11,B14,C14)
Note: in this example we are populating the Array, Row_num and Column_num INDEX function arguments. 		Built-in Excel INDEX Function using links - return the value in the forth row second column from the range

=INDEX((B5:C8,B9:C11),B15,C15,D15)
Note: in this example we are only populating all of the INDEX function arguments. 		Built-in Excel INDEX Function using links - return the value in the third row second column of the second range

METHOD 1. Excel INDEX function using VBA with hardcoded values

VBA

Sub Excel_Index_Function_Using_Hardcoded_Values()

'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("INDEX")
'apply the Excel INDEX function
ws.Range("E14").Value = Application.WorksheetFunction.Index(Range("B5:C11"), 4, 2)
ws.Range("E15").Value = Application.WorksheetFunction.Index(Range("B5:C8,B9:C11"), 3, 2, 2)
ws.Range("E16").Value = Application.WorksheetFunction.Index(Range("B5:D8,B9:D11"), 2, 3, 2)
ws.Range("E17").Value = Application.WorksheetFunction.Index(Range("B5:C8,B9:C11,Index_Defined_Name"), 2, 3, 3)

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named INDEX.
Defined Name: Have a defined name named Index_Defined_Name that comprises a B5:D11 range.

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E14"), ("E15"), ("E16") and ("E17") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.

METHOD 2. Excel INDEX function using VBA with links

VBA

Sub Excel_Index_Function_Using_Links()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("INDEX")
'apply the Excel INDEX function
ws.Range("E14").Value = Application.WorksheetFunction.Index(Range("B5:C11"), Range("B14"), Range("C14"))
ws.Range("E15").Value = Application.WorksheetFunction.Index(Range("B5:C8,B9:C11"), Range("B15"), Range("C15"), Range("D15"))
ws.Range("E16").Value = Application.WorksheetFunction.Index(Range("B5:D8,B9:D11"), Range("B16"), Range("C16"), Range("D16"))
ws.Range("E17").Value = Application.WorksheetFunction.Index(Range("B5:C8,B9:C11,Index_Defined_Name"), Range("B17"), Range("C17"), Range("D17"))

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named INDEX.
Defined Name: Have a defined name named Index_Defined_Name that comprises a B5:D11 range.

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E14"), ("E15"), ("E16") and ("E17") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.

Usage of the Excel INDEX function and formula syntax

EXPLANATION

DESCRIPTION
The Excel INDEX function returns a value that is referenced from a specified range.
SYNTAX
=INDEX(array, row_num, [column_num], [area_num])

ARGUMENTS
array: (Required) An array or reference to a range of cells.
row_num: (Required) A row position relative to the specified range of cells.
column_num: (Optional) A column position relative to the specified range of cells.
area_num: (Optional) The range that you would like to use to return the reference.