📎 This article includes 2 downloadable practice files ↓
In this article
MATCH returns the position of a value in a list — the “where is it?” half of INDEX/MATCH.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])| Argument | What it means |
|---|---|
lookup_value |
What to find. |
lookup_array |
One row or one column to search. |
match_type |
0 = exact (use this), 1 = largest ≤ (sorted ascending), −1 = smallest ≥ (sorted descending). |
Examples
Example 1
=MATCH("Mar", B1:M1, 0)3 — March is the third column.
Example 2
=ISNUMBER(MATCH(A2, Blacklist, 0))TRUE if A2 appears in another list.
Common errors and fixes
| You see | Why, and the fix |
|---|---|
#N/A |
Not found, or match_type omitted on unsorted data. Always use 0 for exact. |
MATCH in practice
Where you will use it
- Position of a month in a header row
- Pair with INDEX for lookups
- Check if a value exists
Worked example
| Find | Formula | Result |
|---|---|---|
| May | =IFERROR(MATCH(A2,{"Apr","May","Jun"},0),"not found") | 2 |
| Jul | =IFERROR(MATCH(A3,{"Apr","May","Jun"},0),"not found") | not found |
| Apr | =IFERROR(MATCH(A4,{"Apr","May","Jun"},0),"not found") | 1 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Leaving out the 0 | Default is approximate match; always use 0 for exact |
| Numbers vs text | "5" doesn't match 5 |
Related functions
INDEX · XMATCH · COUNTIF
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
⬇ Download all 2 files (ZIP · 39 KB)
- 📗Lookup scenarios workbookVLOOKUP, HLOOKUP, INDEX/MATCH and XLOOKUP side by side — vertical, horizontal, two-way — plus 6 real failure cases and their fixes, with PASS/FAIL checks.⬇ XLSX · 10 KB
- 📗Practice workbook: Lookup & reference17 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong