MATCH
MATCH answers "where", never "what", and that difference is where most of the trouble on this hub starts. What comes back is counted from wherever the given range starts rather than from row 1, so the same hit over A5:A20 and over A1:A20 reports different numbers — which is exactly why the pairing with INDEX only works when both ranges begin on the same row. The other recurring failure is structural: hand it a rectangular block instead of a single row or column and the answer is #N/A whatever the block holds. Everything else is the third argument, where omitting the 0 buys a confident wrong position instead of an error.
The formula
=MATCH("Mar", A2:A13, 0) the position of an exact value: 3 when the months start in A2
=MATCH(F2, A2:A500, 0) 0 = exact match, unsorted list, no assumptions
=INDEX(C2:C500, MATCH(F2, A2:A500, 0)) the usual pairing: position first, value second
=MATCH("*north*", A2:A500, 0) wildcards work, but only in exact-match mode and only on text
=IF(A2=B2, "same", "different") simply comparing two cells — not case-sensitive
=IF(EXACT(A2, B2), "same", "different") the case-sensitive comparison
=ISNUMBER(SEARCH("north", A2)) does this one cell contain that word anywhereA worked example
A2:A13 holds the month names January to December in calendar order. F2 holds the text March.
=MATCH(F2, A2:A13, 0)
3 — March is the third item of the range counted from A2, which is not the same as the third row of the sheet; the same formula over A5:A16 would still return 3. Drop the 0 and MATCH treats the list as sorted ascending, which month names are not, so it returns some other position with no error at all. That silent wrong answer is the reason the 0 is always worth typing.
Which one do I need?
| If you want to… | Use |
|---|---|
| Where in this list does my value sit? | =MATCH(value, range, 0) — the answer is a position, counted from the first cell of the range |
| What is on the row with my value? | Wrap it: =INDEX(other_column, MATCH(value, key_column, 0)) |
| Do these two cells hold the same thing? | =A2=B2 for a TRUE/FALSE that ignores case, =EXACT(A2,B2) when case matters |
| Does this cell contain that word somewhere? | =ISNUMBER(SEARCH("north", A2)) — SEARCH ignores case, FIND does not |
| Two columns together identify the row | Join both sides: =MATCH(F2&G2, A2:A500&B2:B500, 0) |
| Names that are nearly but not exactly equal | There is no built-in fuzzy match — use the Fuzzy Lookup add-in, or a Power Query merge with fuzzy matching enabled |
| Every matching row, not only the first | MATCH stops at its first hit by design; FILTER returns the whole set |
| #N/A on a value that is plainly in the list | A block instead of a single column, a missing 0, a trailing space, or a number stored as text |
Frequently asked questions
What does MATCH actually return?
A position inside the range you gave it, never a cell address and never the value. Counting starts at 1 from the first cell of lookup_array, so =MATCH(x, A5:A20, 0) returning 1 means A5, not A1. That relative counting is what makes INDEX the natural partner, since INDEX counts from the same starting point.
Why does MATCH give #N/A when the value is right there?
The most overlooked cause is lookup_array covering more than one column or row — MATCH only accepts a single line of cells. After that come the familiar ones: the third argument was omitted on an unsorted list, the value carries a trailing or non-breaking space, or one side is a number and the other is that number stored as text.
Can MATCH do partial or "contains" matches?
Yes, with wildcards, and only in exact-match mode: * stands for any run of characters and ? for a single one, so =MATCH("*north*", A2:A500, 0) finds the first entry containing north. For a straight yes/no test on one cell rather than a position, =ISNUMBER(SEARCH("north", A2)) is the simpler tool.
Is MATCH case-sensitive?
No — SMITH, Smith and smith are one value as far as MATCH is concerned, and the same is true of COUNTIF, VLOOKUP and XLOOKUP. When case genuinely distinguishes two records, compare with EXACT instead, which is the only text comparison in Excel that respects capitalisation by default.
New guides and tools, once a month
DE + EN · double opt-in · no spam