Syntax
=MATCH(lookup_value, lookup_array, [match_type])| Argument | What it means |
|---|---|
lookup_value | What to find. |
lookup_array | The list to search. |
match_type | 0 for an exact match. 1 (or left out) finds the largest value that is not bigger, in a sorted list. |
Examples with results
Every result below is calculated by the practice engine on the sample data shown, not typed in by hand.
Price list (fictional)
| Row | A | B | C |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P-101 | Desk lamp | 24.9 |
| 3 | P-102 | Notebook | 3.5 |
| 4 | P-103 | Mouse | 15 |
| 5 | P-104 | Keyboard | 32 |
| 6 | P-105 | Monitor stand | 21 |
=MATCH("Mouse",B2:B6,0)Position of Mouse in the Product list
List A and list B (fictional)
| Row | A | B |
|---|---|---|
| 1 | Ordered | Received |
| 2 | P-101 | P-102 |
| 3 | P-102 | P-101 |
| 4 | P-103 | P-105 |
| 5 | P-104 | P-103 |
| 6 | P-105 |
=MATCH("P-104",B2:B6,0)P-104 is not in the Received list, so #N/A
Try it yourself
Change the formula or switch to other sample data.
Press Calculate to see the result.
The practice box runs in your browser with BDK’s own calculator, which covers about 60 common functions. Cell references work as in Excel (B2, B2:B9). Your formulas are not saved or sent anywhere. For anything unusual, check the result in Excel itself.
How people use MATCH at work
- Operations
Find out whether an ordered item was received: a number means yes, #N/A means no.
Data: List A and list B (fictional)=ISNUMBER(MATCH(A2,B2:B6,0)) - General office
Find the position of a heading.
Data: Office supplies sales (fictional)=MATCH("Region",A1:E1,0)
Common mistakes and tips
- Use 0 for the match type unless your list is sorted.
- MATCH returns a position, not the value itself. Combine it with INDEX to get the value.
Works in
All versions of Excel, and Google Sheets.
Lessons that use it
- Rank students and find the top scoresIntermediate lesson, about 7 min
- Look up a price from a price listIntermediate lesson, about 8 min
- Check which items from one list are in anotherIntermediate lesson, about 7 min
- Measure marketing campaign resultsIntermediate lesson, about 7 min
- Pick a delivery price from a two-way gridAdvanced lesson, about 7 min
Need Something Different? Let’s Talk.
Looking for different features or something built around your workflow? Share your idea with us.