The MATCH function searches for a value within a range and returns the relative position where the value is found. This guide explains how MATCH works and includes practical examples you can use immediately.
WHAT THE MATCH FUNCTION DOES
MATCH locates a value within a range and returns its position as a number, making it the perfect partner for INDEX — MATCH finds the position, INDEX retrieves the value at that position. This combination provides more flexibility than VLOOKUP, allowing lookups in any direction.
WHEN TO USE MATCH
- You want to find where a specific value appears in a list
- You're building dynamic lookups with INDEX/MATCH combination
- You need more flexibility than VLOOKUP's fixed column structure
- You want to find the position for sorting or ranking purposes
FORMULA SYNTAX
MATCH(lookup_value, lookup_array, [match_type])| Argument | Description |
|---|---|
lookup_value | The value you want to find |
lookup_array | The range to search in (single row or column) |
match_type | 0 = exact match, 1 = less than, -1 = greater than (optional) |
EXAMPLE 1: FIND POSITION OF A PRODUCT
We want to find which row contains "Widget B" in our product list.
| A | B | |
|---|---|---|
| 1 | Product | Position |
| 2 | Widget A | |
| 3 | Widget B | |
| 4 | Widget C | |
| 5 | Widget D | |
| 6 | ||
| 7 | Find: | Widget B |
| 8 | Position: | =MATCH(B7,A2:A5,0) |
=MATCH("Widget B", A2:A5, 0)Result: 2 (Widget B is in the 2nd position within range A2:A5)
Need help applying this? Try this in Numstro Builder
EXAMPLE 2: DYNAMIC LOOKUP WITH INDEX AND MATCH
We want to look up any employee's salary using their name.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Department | Salary |
| 2 | Alice | Sales | 50000 |
| 3 | Bob | Engineering | 75000 |
| 4 | Carol | Marketing | 60000 |
| 5 | |||
| 6 | Employee: | Bob | =INDEX(C2:C4,MATCH(B6,A2:A4,0)) |
=INDEX(C2:C4, MATCH("Bob", A2:A4, 0))Result: 75000 (MATCH finds Bob at position 2, INDEX gets salary from row 2)
Need help applying this? Try this in Numstro Builder
COMMON MISTAKES & EDGE CASES
- Use match_type 0 for exact matches (most common use case)
- Match_type 1 requires sorted ascending data, -1 requires sorted descending
- Returns #N/A error if the lookup value isn't found with exact match
- MATCH works with single-dimension ranges (one row OR one column, not both)
- Position returned is relative to the search range, not the entire worksheet
RELATED FUNCTIONS
FURTHER READING
Official Microsoft documentation:
MATCH — Microsoft Support