MATCH: Find Position of Values (2025)
Quick Answer: MATCH finds position: =MATCH('Apple', A1:A10, 0) returns row number where Apple is found. Returns position (not value). Match types: 0=exact, 1=sorted ascending, -1=sorted descending. Combine with INDEX for powerful lookups.
What is MATCH?
MATCH searches for a value in a range and returns its position (row or column number). It doesn't return the value itself - just where it is. Think of it as a GPS that tells you the location, not what's there.
Simple Example:
| Row | Product (A) |
|---|---|
| 1 | Apple |
| 2 | Banana |
| 3 | Orange |
| 4 | Grape |
=MATCH("Orange", A1:A4, 0)Returns: 3 (Orange is in position 3)
MATCH Returns Position, Not Value:
What MATCH Does:
=MATCH("Apple", A1:A10, 0)
Returns: 3 (position)
What INDEX Does:
=INDEX(A1:A10, 3)
Returns: "Apple" (value)
Three Match Types:
0 - Exact Match (Most Common)
=MATCH("Apple", A:A, 0)
Finds exact "Apple". No sorting needed. Returns #N/A if not found.
1 - Largest Value ≤ Lookup (Default)
=MATCH(85, Breakpoints, 1)
Requires sorted ascending. 85 finds 80 if list is {70, 80, 90}.
-1 - Smallest Value ≥ Lookup (Rare)
=MATCH(85, Breakpoints, -1)
Requires sorted descending. 85 finds 90 if list is {100, 90, 80}.
Why Use MATCH?
With INDEX: Create flexible lookups (INDEX/MATCH)
Find position: Know where data is located
Dynamic references: Build formulas that adjust automatically
Check existence: Test if value exists (returns #N/A if not)
💡 Common Use Cases
- • INDEX/MATCH combo: Flexible alternative to VLOOKUP
- • Two-way lookups: Find row and column positions
- • Dynamic column selection: Reference columns by header name
- • Data validation: Check if value exists in list
- • Ranking positions: Find where value ranks
MATCH Syntax
=MATCH(lookup_value, lookup_array, [match_type])
Arguments
lookup_value (required)
Value to search for (text, number, logical, or cell reference)
"Apple" or A2 or 100lookup_array (required)
Single row or column to search (must be 1-dimensional)
A:A or 1:1 or Products[Column]match_type (optional)
How to match (default: 1)
0 - Exact match
1 - Largest value ≤ lookup (sorted ascending)
-1 - Smallest value ≥ lookup (sorted descending)
Common Patterns
Exact Match (Most Common)
=MATCH(A2, Products, 0)
With INDEX for Lookup
=INDEX(Prices, MATCH(A2, Products, 0))
Two-Way Lookup
=INDEX(Data, MATCH(Row, Rows, 0), MATCH(Col, Cols, 0))
Find Column by Header
=MATCH("Price", 1:1, 0)
Grade Bracket (Approximate)
=MATCH(85, {0,60,70,80,90}, 1)
Match Type Comparison
| Type | Behavior | Requires Sorting | Use Case |
|---|---|---|---|
| 0 | Exact match | No | Most lookups |
| 1 | Largest ≤ value | Yes (ascending) | Grade brackets, bins |
| -1 | Smallest ≥ value | Yes (descending) | Reverse sorted data |
⚠️ Important Notes
- • Returns #N/A: If value not found (use IFERROR to handle)
- • Case insensitive: "apple" matches "APPLE"
- • 1-dimensional only: Search single row OR column, not 2D array
- • Wildcards work: Use * and ? with match_type 0
- • First match: Returns position of first occurrence
- • Default is 1: Omitting match_type = 1 (approximate match)
10+ MATCH Examples
Basic MATCH
1. Find Position (Exact Match)
=MATCH("Banana", A2:A10, 0)Returns position number where "Banana" is found
2. Find Column by Header
=MATCH("Price", A1:Z1, 0)Returns column number of "Price" header
3. Check If Value Exists
=IF(ISNA(MATCH(A2, ValidList, 0)), "Invalid", "Valid")
Returns "Valid" if found, "Invalid" if not
INDEX/MATCH Combinations
4. Simple Lookup
=INDEX(B:B, MATCH(A2, A:A, 0))
Find A2 in column A, return corresponding B value
5. Left Lookup (VLOOKUP Can't Do)
=INDEX(A:A, MATCH(C2, D:D, 0))
Search column D, return column A (left of search)
6. Two-Way Lookup (Matrix)
=INDEX(B2:E10, MATCH(A2, A2:A10, 0), MATCH(B1, B1:E1, 0))
Find value by matching both row and column headers
Approximate Match (Sorted Data)
7. Grade Bracket
=INDEX({"F","D","C","B","A"}, MATCH(Score, {0,60,70,80,90}, 1))85 returns "B" (80-89 bracket)
8. Tax Bracket
=INDEX(TaxRates, MATCH(Income, Thresholds, 1))
Find tax rate based on income threshold
Advanced Techniques
9. Wildcard Search
=MATCH("*apple*", A:A, 0)Finds "Green Apple", "Apple Pie", etc.
10. Dynamic Column Reference
=INDEX(Data, ROW(), MATCH(ColumnName, Headers, 0))
Reference column by name instead of number
11. Last Occurrence
=LOOKUP(2, 1/(A:A="Apple"), ROW(A:A))
Find last row where "Apple" appears
🎯 Pro Tips
- • Always use 0 for exact match: Unless data is specifically sorted
- • Wrap in IFERROR: Handle #N/A errors gracefully
- • Wildcards work: Use * (any characters) and ? (single character)
- • Case insensitive: "apple" = "APPLE" = "Apple"
- • First match only: Returns position of first occurrence
- • Performance: Very fast even on 100k+ rows
Frequently Asked Questions
Why does MATCH return #N/A?
#N/A means value not found in lookup array. Common causes: (1) Exact spelling doesn't match (extra spaces, typos), (2) Wrong match_type (using 1 when need 0), (3) Data not sorted for approximate match, (4) Value truly doesn't exist. Use IFERROR(MATCH(...), 'Not Found') to handle gracefully.
What's the difference between MATCH type 0, 1, and -1?
0: Exact match (most common), no sorting needed. 1: Finds largest value ≤ lookup (default), requires ascending sort. Example: 85 finds 80 in {60,70,80,90}. -1: Finds smallest value ≥ lookup, requires descending sort. Always use 0 unless working with sorted brackets/bins.
Can MATCH search 2D arrays?
No. MATCH only searches single row or column. For 2D: Use two MATCH functions with INDEX: =INDEX(array, MATCH(row_val, row_range, 0), MATCH(col_val, col_range, 0)). First MATCH finds row, second finds column, INDEX returns intersection.
How do I find the last occurrence instead of first?
MATCH only finds first. For last: Use array formula =LOOKUP(2, 1/(A:A=value), ROW(A:A)) or in Excel 365: =XMATCH(value, range, 0, -1). Alternative: Reverse your data order and use regular MATCH.
Does MATCH work with wildcards?
Yes, with match_type 0. Use * (any characters) or ? (single character). Example: =MATCH('*apple*', A:A, 0) finds 'Green Apple', 'Apple Pie'. Wildcards only work with text and exact match (type 0), not approximate matches.
Why use INDEX/MATCH instead of VLOOKUP?
INDEX/MATCH advantages: (1) Look left (VLOOKUP can't), (2) Insert/delete columns won't break formula, (3) Slightly faster on large data, (4) More flexible (can swap lookup and return ranges). VLOOKUP is simpler for basic right-side lookups. Use INDEX/MATCH for complex lookups.