The MATCH function searches for a specified value in a range and returns the relative position of that value. MATCH is often combined with INDEX to create powerful, flexible lookup formulas that can work in any direction, surpassing the limitations of VLOOKUP and HLOOKUP.
=MATCH(lookup_value, lookup_array, match_type)The value to search for. Can be a number, text, logical value, or cell reference containing any of these.
A range of cells or an array constant containing the values to search. Must be a single row or single column.
The type of match: 1 (or omitted) for less than or equal, 0 for exact match, -1 for greater than or equal. Use 0 for exact matches.
Position of lookup_value in lookup_array (1-based position)
Description: Returns the relative position of an item in an array that matches a specified value
Find position of value in range
Returns 3 because "Cherry" is the third item in the range. Always use 0 for exact matches.
Master the fundamentals of Excel MATCH function
The MATCH function is Excel's position-finding tool that locates a value within a range and returns its relative position. MATCH is fundamental to creating flexible lookup formulas when combined with INDEX, allowing lookups that work in any direction and overcome VLOOKUP/HLOOKUP limitations.
Returns 1-based position of value in array
0=exact, 1=<= (sorted asc), -1=>= (sorted desc)
Powerful INDEX/MATCH combination
Works with any lookup direction
Function-specific parameters
Function-specific return type
Find position of values in ranges
Combine with INDEX for any-direction lookups
Find both row and column positions
Create dynamic cell references
Exact matching required
Returns numeric position
Handles missing text gracefully
=MATCH(lookup_value, lookup_array, match_type)Value to search for
Single row or column range to search
0=exact, 1=<= (sorted asc), -1=>= (sorted desc), default=1
1-based position of lookup_value in array, or #N/A if not found
Description: Returns the relative position of a value in an array
Find position
Returns position of "Banana"
Using MATCH in VBA
Sub MATCHExample()
' Method 1: Using WorksheetFunction.Match
Dim position As Variant
position = Application.WorksheetFunction.Match("Banana", Range("A1:A5"), 0)
Range("B1").Value = position
' Method 2: MATCH with INDEX
Dim lookupValue As String
Dim rowNum As Variant
Dim result As Variant
lookupValue = "John"
rowNum = Application.WorksheetFunction.Match(lookupValue, Range("A1:A10"), 0)
result = Application.WorksheetFunction.Index(Range("B1:B10"), rowNum)
Range("C1").Value = result
' Method 3: 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("D1").Value = result
' Method 4: Approximate match (sorted ascending)
Dim score As Integer
Dim gradePosition As Variant
score = 85
gradePosition = Application.WorksheetFunction.Match(score, Range("A1:A5"), 1)
Range("E1").Value = gradePosition
' Method 5: Error handling
On Error Resume Next
position = Application.WorksheetFunction.Match("NotFound", Range("A1:A5"), 0)
If Err.Number <> 0 Then
Range("F1").Value = "Not Found"
Err.Clear
Else
Range("F1").Value = position
End If
On Error GoTo 0
End SubFlexible lookups in any direction
Find both row and column positions
Look up values to the left of lookup column
Find positions for dynamic references
Lookup value not found in array
=IFERROR(MATCH(Value, Range, 0), "Not Found")Solution: Verify the lookup value exists in lookup_array. Check for typos, case sensitivity, extra spaces, and data type mismatches. Use TRIM and exact match (0). Consider using IFERROR to handle gracefully.
Incorrect results with approximate match
=MATCH(Value, Range, 0) for exact matchSolution: For match_type 1, array must be sorted ascending. For match_type -1, array must be sorted descending. Use exact match (0) unless you specifically need approximate matching.
Error when using 2D range
MATCH works with A1:A10, not A1:B10Solution: MATCH requires a single row or single column. Use INDEX to extract a row/column first, or use a single-dimension range like A1:A10 (not A1:B10).
Slow performance with large unsorted ranges
Use 0 for exact match with unsorted dataSolution: Use exact match (0) for unsorted data - it's more reliable. Approximate match requires sorted data. Consider sorting data if you need approximate matching for performance.