The INDEX function returns a value or reference to a cell from within a table or range. INDEX has two forms: array form (returns value) and reference form (returns cell reference). It's fundamental for dynamic lookups and often combined with MATCH for powerful lookup operations.
=INDEX(array, row_num, column_num)A range of cells or an array constant from which to return a value. For array form, this is the table or range.
The row number in the array from which to return a value. Use 0 to return entire row. For array form with one row, can be omitted.
The column number in the array from which to return a value. Use 0 to return entire column. Required for 2D arrays.
Value or reference from specified position in array
Description: Returns a value or reference to a cell from within a table or range using row and column numbers
Return value by row and column number
Returns the value from the intersection of row 2 and column 3 in the range A1:C3. Simple and direct lookup by position.
Master the fundamentals of Excel INDEX function
The INDEX function is one of Excel's most powerful lookup tools, returning values or references by position within an array. It has two forms: array form (returns values) and reference form (returns cell references). INDEX is often combined with MATCH to create flexible lookups that surpass VLOOKUP/HLOOKUP limitations.
Returns value by row and column position
Works left-to-right or right-to-left
Creates dynamic references
Powerful INDEX/MATCH combination
Function-specific parameters
Function-specific return type
Look up values in any direction
Create dynamic cell references
Look up by both row and column
Extract entire rows or columns
Exact matching required
Returns numeric position
Handles missing text gracefully
=INDEX(array, row_num, column_num)Range or array from which to return value
Row number (use 0 for entire row)
Column number (use 0 for entire column, required for 2D arrays)
Value or reference from specified position
Description: Returns value or reference by row and column position
Get value by position
Returns value from specified position
Using INDEX in VBA
Sub INDEXExample()
' Method 1: Using WorksheetFunction.Index (array form)
Dim result As Variant
result = Application.WorksheetFunction.Index(Range("A1:C3"), 2, 3)
Range("D1").Value = result
' Method 2: INDEX with MATCH
Dim lookupValue As String
Dim rowNum As Variant
lookupValue = "John"
rowNum = Application.WorksheetFunction.Match(lookupValue, Range("A1:A5"), 0)
result = Application.WorksheetFunction.Index(Range("B1:B5"), rowNum)
Range("D2").Value = result
' Method 3: Dynamic range with INDEX
Dim lastRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Dim dynamicRange As Range
Set dynamicRange = Range("A1:" & Range("A" & lastRow).Address)
Range("D3").Value = Application.WorksheetFunction.Index(dynamicRange, lastRow)
' Method 4: Two-way lookup
Dim rowIndex As Variant
Dim colIndex As Variant
rowIndex = Application.WorksheetFunction.Match("Product", Range("A1:A10"), 0)
colIndex = Application.WorksheetFunction.Match("Price", Range("A1:Z1"), 0)
result = Application.WorksheetFunction.Index(Range("A1:Z10"), rowIndex, colIndex)
Range("D4").Value = result
' Method 5: Return entire row
Dim entireRow As Variant
entireRow = Application.WorksheetFunction.Index(Range("A1:D5"), 3, 0)
Range("E1:E1").Resize(1, UBound(entireRow)).Value = entireRow
End SubLook up by both row and column
Look up left of lookup column
Create ranges that adjust automatically
Extract rows or columns dynamically
Row or column number out of range
=INDEX(A1:C3, 2, 3)Solution: Verify row_num and column_num are within array bounds. For A1:C3, valid rows are 1-3, valid columns are 1-3. Check that the array range is correct.
Incorrect row or column numbers
Verify row and column numbers are correctSolution: Remember INDEX uses 1-based indexing (first row/column is 1, not 0). Double-check row_num and column_num match the intended position.
MATCH cannot find lookup value
=INDEX(Range, MATCH(Value, LookupRange, 0))Solution: The issue is with MATCH, not INDEX. Verify lookup value exists in lookup range. Check for typos, case sensitivity, and extra spaces. Use exact match (0) unless you need approximate.
Slow performance with large INDEX operations
Use specific ranges instead of entire columnsSolution: Limit array size to necessary data only. For array formulas with INDEX, consider using dynamic arrays or optimizing the range. INDEX/MATCH is generally faster than VLOOKUP.