Why Is My MATCH Formula Not Working in Microsoft Excel?

Most MATCH formulas fail because the match type is wrong, the lookup value differs from the data, or the lookup range is not what the formula expects. For an exact match, use match type 0: with the value to find in D2 and the list in A2:A6, enter =MATCH(D2,A2:A6,0). MATCH returns the value’s position within A2:A6, not its worksheet row number.

What MATCH does and how its arguments work

MATCH searches one row or one column for a value and returns its relative position. If the value in D2 is found in the fourth cell of A2:A6, the result is 3, because A4 is the third item in that five-cell lookup range. This distinction often explains a result that looks wrong: MATCH reports a position in the selected range, not a cell address or the number 4 from the worksheet row label.

The function has three arguments: the value to look for, the one-dimensional range to search, and an optional match type. The match type controls what counts as a match and whether Excel can use approximate matching. If you leave it out, Excel uses 1, which is a frequent source of unexpected results. For names, product codes, and other searches that need an exact result, explicitly use 0.

MATCH is commonly combined with INDEX to return information from a neighboring column. With product names in A2:A6, prices in B2:B6, and the requested product name in D2, this returns the price from the matching row: =INDEX(B2:B6,MATCH(D2,A2:A6,0)). The lookup and return ranges must line up so the position MATCH finds points to the corresponding item in the return range.

Start with the exact-match setting

For most troubleshooting, check the third argument first. Match type 0 asks Excel to find an exact match and does not require the list to be sorted. With 1, MATCH looks for the largest value less than or equal to the lookup value and expects the lookup list to be sorted in ascending order. With -1, it looks for the smallest value greater than or equal to the lookup value and expects the list to be sorted in descending order.

Approximate matching is useful in deliberate cases such as grade bands or tax brackets, where a value should fall into a range. It is usually inappropriate for finding a particular name or ID. If an approximate-match list is not sorted as required, Excel may return a plausible-looking but incorrect position rather than an obvious error. Add 0 unless approximate matching is intentional.

For instance, if D2 contains 25 and A2:A6 contains unsorted values, an approximate search can behave unexpectedly. With the same layout, use =MATCH(D2,A2:A6,0) when only the exact number 25 should count. If the list instead represents ordered thresholds, sort it in the direction required by the selected match type and confirm that the returned position corresponds to the intended band.

Fix #N/A by checking the value and the source data

#N/A means MATCH did not find a value it considers a match. The cells may look identical while containing different underlying data. Common causes include a trailing space, a nonbreaking space copied from a webpage, a different punctuation mark, a misspelled character, or a number stored as text in one place and as a number in the other. Formatting a cell to look like a number does not necessarily convert text into a numeric value.

Compare the lookup value with a cell in the list in the formula bar, not only on the worksheet.
Check for leading or trailing spaces and characters copied from external sources. TRIM can remove ordinary extra spaces, but it does not remove every nonprinting or nonbreaking character.
Check whether both values are the same data type. Convert imported numeric text to real numbers where appropriate, or standardize both sides as text.
Confirm that the lookup value is actually inside the stated lookup range; a nearby column or a row outside the selected range will not be searched.

For text that needs cleaning, do the cleanup in helper columns and compare the cleaned values before changing the main formula. Blindly adding cleanup functions can conceal inconsistent source data, and it may not deal with every unusual character. For imported data, reimporting or transforming the source consistently is often more reliable than patching individual cells.

Check text, numbers, wildcards, and capitalization

Exact does not mean case-sensitive: MATCH does not distinguish uppercase from lowercase. A search for “north” can match “North.” If capitalization must determine the result, MATCH alone is not enough; a case-sensitive approach needs a different formula design.

When the match type is 0 and the lookup value is text, the asterisk and question mark act as wildcards. An asterisk represents any number of characters; a question mark represents one character. To find the first entry beginning with AB, with the lookup list in A2:A6, use =MATCH("AB*",A2:A6,0). If you need to search for a literal asterisk or question mark, prefix that character with a tilde in the search text. Wildcards apply to text matching, not to an ordinary numeric approximate search.

Also verify that numbers and text have not been mixed. An ID such as 00127 may be intended as text because its leading zeros matter, while a quantity such as 127 is numeric. Choose a consistent representation on both sides rather than removing leading zeros automatically. Excel’s display format can make a number appear padded without changing the underlying value, so visual comparison alone can be misleading.

Make sure the lookup range has the right shape

MATCH searches one row or one column, not a two-dimensional table. Selecting A2:C20 when the intended list is in column A can cause confusion or an error because the lookup array should be a single row or column. Select only the list that contains the values being searched. The lookup value itself can be in another cell or typed directly into the function.

Check the range boundaries, too. If new records were added below A2:A20 but the formula still searches only A2:A20, those records are invisible to MATCH. A structured Excel table can expand its references as rows are added, or you can update the range. When copying a formula, use absolute references for a lookup list that should stay fixed. With the lookup value in D2 and the list in A2:A20, the fixed-range version is =MATCH(D2,$A$2:$A$20,0). The dollar signs keep the list from shifting when the formula is copied.

If MATCH is nested inside INDEX, verify that both ranges begin and end on corresponding rows. A position of 3 from A2:A6 refers to the third item in that range. If the return range starts at B3 instead of B2, the same position points to a different worksheet row. This kind of alignment error can return a valid-looking but incorrect answer.

Understand the error or unexpected result

#N/A is the most common MATCH error and usually means there is no qualifying match in the specified range. Start by confirming the exact-match setting and checking for data differences. #VALUE! often points to an invalid argument or a problem in a surrounding formula; inspect the full formula, especially if MATCH is nested inside another function. #REF! indicates an invalid cell reference, such as a range that was deleted or changed. A number that is simply not the position you expected is more likely to involve approximate matching, range alignment, or the distinction between position and worksheet row.

Do not immediately wrap every search in IFERROR just to hide #N/A. That can make a workbook look tidy while hiding misspelled IDs or broken references. If “not found” is a normal outcome, handle it deliberately after confirming the underlying search logic. If it is unexpected, keep the error visible while investigating.

There is also a difference between a formula that calculates incorrectly and one Excel is not calculating. If the cell displays the formula text, the cell may be formatted as Text, the entry may have a leading apostrophe, or Show Formulas may be enabled. Change the cell’s format to General, then re-enter the formula. If a formula result stays stale, check Formulas > Calculation Options and choose Automatic if that is appropriate for the workbook.

A reliable troubleshooting sequence

Work through the checks in order, changing one thing at a time. That makes it easier to identify the actual cause instead of masking it with a different formula.

Confirm the formula begins with an equals sign and that the cell is not treating it as text.
Identify the one-dimensional range MATCH should search, and verify that the target value falls within its boundaries.
Set the third argument to 0 unless you intentionally need an approximate match.
Compare the lookup value and a likely source cell for spelling, spaces, punctuation, data type, and hidden characters.
Check whether MATCH’s returned position is being interpreted relative to the lookup range, and align any INDEX return range with it.
If the result is still unexpected, test the search in a spare cell using a known value from the list. This separates a MATCH problem from an issue in a larger nested formula.

For example, if a known entry is in A4 and the lookup range starts at A2, the exact-match test should return 3, not 4. If it returns #N/A, check whether the value in the test cell is truly the same as A4 and whether the formula references the intended column. If it returns 3 but the larger formula gives the wrong output, focus next on the return range or the other functions in that formula.

When XMATCH or another approach makes more sense

In Excel for Microsoft 365 and Excel 2021, XMATCH is a newer alternative that uses exact matching by default and offers additional search options. It can make the intended behavior clearer when an exact search is what you want. With the lookup value in D2 and the list in A2:A6, the exact XMATCH version is =XMATCH(D2,A2:A6,0). XMATCH also returns a position, so it does not by itself return a related value from another column.

For an older workbook that must work in earlier Excel versions, MATCH remains widely compatible. If the goal is to return a corresponding value rather than just a position, INDEX with MATCH is a standard option. Excel for Microsoft 365 and Excel 2021 also include XLOOKUP, which can search a lookup range and return a corresponding result directly. Choose based on the workbook’s compatibility needs, not just the shortest formula, and keep the underlying lookup values consistent whichever method you use.

Frequently Asked Questions

Why does MATCH return #N/A when the value is in the list?
It may only appear to be the same. Check for extra or hidden spaces, punctuation differences, text-versus-number differences, and whether the formula’s range includes the cell. For a normal exact search, confirm that the third argument is 0.

Why does MATCH return the wrong row number?
MATCH returns a position counted from the start of its lookup range, not a worksheet row number. If the match is in A4 and the range starts at A2, the position is 3. An approximate match or misaligned INDEX range can also produce an unexpected result.

Does MATCH distinguish uppercase and lowercase?
No. MATCH is not case-sensitive, so uppercase and lowercase versions of otherwise identical text are treated as a match. Use a case-sensitive formula approach if capitalization must be distinguished.

Why does MATCH work in one row but not after I copy it?
The lookup range may have shifted as the formula was copied. Use absolute references, such as dollar signs around the range, when it must remain fixed. Also check that the lookup-value reference changes as intended.

Should I use 0 or leave the match type blank?
Use 0 for an exact match, which is the usual choice for names and IDs. Leaving the argument blank makes MATCH use approximate matching with type 1, which expects the list to be sorted in ascending order.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.