📎 This article includes 1 downloadable practice file ↓
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. |
Related functions
INDEX · XMATCH · COUNTIF
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
📎 Practice files for this article
- 📗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
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