Need to find the position of a value in a list? MATCH returns the row or column number where a value appears, making it perfect for lookups, dynamic references, and building flexible formulas. It’s the ideal partner for INDEX.
What Makes MATCH Useful
MATCH locates values precisely:
- Find position – Get the exact row or column number of any value
- Flexible matching – Exact matches, approximate matches, or wildcards
- Dynamic formulas – Build references that adapt automatically
- INDEX partner – The essential companion to INDEX for powerful lookups
- Search direction – Works with both rows and columns
The Syntax
=MATCH(lookup_value, lookup_array, [match_type])Lookup_value is what you’re searching for. Lookup_array is where to search. Match_type determines how to match (0 = exact, 1 = less than, -1 = greater than).
Example 1: Basic Position Finding
Find where “Keyboard” appears in the list:
| Product |
|---|
| Laptop |
| Mouse |
| Keyboard |
| Monitor |
Formula: =MATCH("Keyboard", A2:A5, 0)
Result: 3
“Keyboard” is the 3rd item in the range A2:A5.
Example 2: Finding Column Position
Find which column contains “Q3”:
| Q1 | Q2 | Q3 | Q4 | |
|---|---|---|---|---|
| Sales | … | … | … | … |
Formula: =MATCH("Q3", B1:E1, 0)
Result: 3
“Q3” is the 3rd item in the horizontal range.
Example 3: Exact Match (Match Type 0)
Match_type 0 finds exact matches only:
| Employee | Department |
|---|---|
| Alice | Sales |
| Bob | IT |
| Carol | Marketing |
Formula: =MATCH("IT", B2:B4, 0)
Result: 2
“IT” is found at position 2. If not found, returns #N/A error.
Example 4: Approximate Match (Match Type 1)
Find the highest value that’s less than or equal to lookup value:
| Sales Threshold |
|---|
| $0 |
| $10,000 |
| $25,000 |
| $50,000 |
Formula: =MATCH(32000, A2:A5, 1)
Result: 3
32,000 falls between $25,000 and $50,000. Returns position 3 ($25,000). List must be sorted ascending.
Example 5: Combining with INDEX
Find the price for a specific product:
| Product | Price |
|---|---|
| Laptop | $4,500 |
| Mouse | $250 |
| Keyboard | $800 |
Formula: =INDEX(B2:B4, MATCH("Mouse", A2:A4, 0))
Result: $250
MATCH finds position 2, INDEX retrieves the value at position 2 in the price column.
Example 6: Two-Way Lookup with Double MATCH
Find a specific value in a table:
| Q1 | Q2 | Q3 | Q4 | |
|---|---|---|---|---|
| Product A | $125K | $138K | $145K | $162K |
| Product B | $98K | $105K | $112K | $119K |
Formula: =INDEX(B2:E3, MATCH("Product B", A2:A3, 0), MATCH("Q3", B1:E1, 0))
Result: $112K
First MATCH finds the row, second MATCH finds the column, INDEX retrieves the value.
Match Type Options
| Match Type | Behavior | List Order Required |
|---|---|---|
| 0 | Exact match | No sorting needed |
| 1 | Largest value ≤ lookup | Sorted ascending |
| -1 | Smallest value ≥ lookup | Sorted descending |
Common Use Cases
Dynamic Column Reference Find which column contains a header, then use INDEX to get data.
Rank Finding Determine where a value ranks in a sorted list.
Data Validation Check if a value exists before processing.
Flexible Lookups Build formulas that don’t break when columns move.
MATCH with Wildcards
Use wildcards with match_type 0:
| Name |
|---|
| John Smith |
| Sarah Johnson |
| Mike Williams |
Formula: =MATCH("*Johnson*", A2:A4, 0)
Result: 2
Asterisk (*) matches any characters. Question mark (?) matches single character.
Common MATCH Patterns
Check if value exists:
=ISNUMBER(MATCH("value", A:A, 0))Returns TRUE if found, FALSE if not.
Find last occurrence:
=MATCH(2, 1/(A:A="value"), 1)Returns position of last match.
Case-sensitive match:
=MATCH(TRUE, EXACT(A:A, "Value"), 0)EXACT is case-sensitive, MATCH finds the TRUE result.
MATCH vs Similar Functions
| Function | Purpose | Returns |
|---|---|---|
| MATCH | Find position | Row or column number |
| SEARCH | Find character position | Character number in text |
| FIND | Find character position (case-sensitive) | Character number |
| VLOOKUP | Find and return value | The value itself |
Common Mistakes to Avoid
Wrong Match Type
- Match_type 1 requires ascending sort
- Match_type -1 requires descending sort
- If unsorted, use 0 for exact match only
Array vs Single Cell
- Lookup_array must be a single row or column
- Can’t search a 2D range directly
- Use separate MATCH for row and column
Forgetting Error Handling
- MATCH returns #N/A when value not found
- Wrap in IFERROR or IFNA for user-friendly messages
Error Handling
Provide default if not found:
=IFERROR(MATCH("value", A:A, 0), "Not in list")Check before using:
=IF(ISNUMBER(MATCH("value", A:A, 0)), "Found", "Not found")Advanced Techniques
Multiple criteria match:
=MATCH(1, (A:A="value1")*(B:B="value2"), 0)(Array formula – Ctrl+Shift+Enter in older Excel)
Match closest value:
=MATCH(MIN(ABS(A:A-value)), ABS(A:A-value), 0)Match within range:
=MATCH(TRUE, (A:A>=lower)*(A:A<=upper), 0)Practical Combinations
Dynamic named range:
=OFFSET(A1, 0, 0, MATCH("End", A:A, 0), 1)Conditional lookup:
=INDEX(C:C, MATCH(1, (A:A="criteria1")*(B:B="criteria2"), 0))Find nth occurrence:
=SMALL(IF(A:A="value", ROW(A:A)), n)When to Use MATCH
Use MATCH when:
- You need position numbers, not values
- Building INDEX-MATCH lookups
- Creating dynamic references
- Position matters more than the value
Don’t use MATCH when:
- You just need to check if value exists (use COUNTIF)
- You want the value itself (use VLOOKUP or INDEX-MATCH)
- Searching 2D ranges (use XMATCH or multiple MATCH)
Start Using It
Open a spreadsheet with a list. Pick a value to find. Type =MATCH, specify the value and range. See the position number returned.
MATCH becomes powerful when combined with INDEX for flexible lookups.
Questions about MATCH? Ready to build advanced INDEX-MATCH formulas? Let’s connect.