Excel MATCH Errors: Causes, Fixes, and Formula Examples

The most common Excel MATCH error is #N/A: MATCH cannot find the lookup value in the specified range. For an exact lookup, set the third argument to 0; with the target value in D2 and the list Pear, Plum, Apple, Orange, and Kiwi in A2:A6, =MATCH(D2,A2:A6,0) returns 1 when D2 contains Pear.

What MATCH does and why it returns an error

MATCH searches a one-dimensional range and returns the position of a matching item within that range. It does not return the item itself. In the example above, Pear is in the first cell of A2:A6, so the result is 1—not the worksheet row number 2. This distinction matters when you use MATCH by itself or combine it with another function.

The function’s syntax is MATCH(lookup_value, lookup_array, [match_type]). The first argument is what you are looking for, the second is the single row or column to search, and the optional third argument controls how Excel matches and whether the range must be sorted. If you omit that argument, Excel uses 1, which is an approximate-match mode—not an exact-match default. That default is a frequent source of unexpected results.

A MATCH error is not always evidence that the value is visibly absent. Excel compares underlying values, not just how cells look on screen. A number stored as text, an extra space, a wrong range, or an unsuitable match mode can all make a lookup fail or return a position that seems wrong.

Choose the right match type

For most everyday lookups, use 0 for an exact match. This is especially important for unsorted lists, product codes, names, invoice numbers, and other values where a near match is not acceptable. MATCH searches for an equal value and returns the position of its first occurrence.

0: Finds an exact match. The lookup range does not need to be sorted. For text, Excel can also interpret the wildcards * and ?; a tilde (~) escapes a wildcard character when you need to search for it literally.
1, or omitted: Finds the largest value less than or equal to the lookup value. The range must be sorted in ascending order. This mode is useful for threshold tables, such as tax bands or commission tiers, when the data is arranged correctly.
-1: Finds the smallest value greater than or equal to the lookup value. The range must be sorted in descending order.

Approximate matching is not simply a looser version of exact matching. It relies on the ordering of the data, so an unsorted range can produce an incorrect position without an obvious warning. If you are not deliberately using a sorted threshold table, choose 0 explicitly.

Fixing the common #N/A error

#N/A usually means Excel did not find a match under the rules in the formula. First check that the lookup value is actually present in the range, then confirm the range covers the intended cells and that the formula uses the match type you meant to use. If the value is present but the error remains, investigate differences that are easy to miss:

Extra spaces: A cell might contain a leading or trailing space, or a nonbreaking space copied from a website. The displayed text can look identical while the underlying strings differ.
Different data types: The number 123 and the text value “123” are not necessarily interchangeable in a lookup. Check whether one side is stored as text, especially for imported data or codes with leading zeros.
Different characters: A regular hyphen and a longer dash, or a straight apostrophe and a typographic apostrophe, are distinct characters. Check for spelling, punctuation, and hidden characters.
Wrong range or reference: Make sure the formula points to the column or row containing the values and that a copied formula has not shifted its range. Dollar signs can lock a range when you fill a formula down.
Wildcard interpretation: With match type 0, * and ? in a text lookup value can act as wildcards. If you intend to search for one literally, precede it with ~.

For example, if a list came from an external system, compare the suspect cell with a manually retyped value and inspect the formula bar. TRIM can remove ordinary leading and trailing spaces in a helper column, but it does not remove every kind of invisible character. For imported text, CLEAN can remove certain nonprinting characters; nonbreaking spaces may need to be replaced separately. Clean or normalize both the lookup value and the source list consistently, rather than changing only one side.

Other errors and confusing results

#VALUE! or another formula error can indicate a problem with an argument, a referenced cell, or a calculation feeding MATCH. Confirm that the lookup array is a single row or a single column, not a two-dimensional block, and that the match type is one of the supported values: -1, 0, or 1. If a referenced cell already contains an error, that error may also flow into the lookup formula.

#REF! points to a broken reference, such as a range that was deleted or changed in a way that invalidated the formula. Edit the formula to select the correct surviving range. #NAME? often indicates a misspelled function or a text lookup value typed without quotation marks when it should be a literal. If the lookup value is a cell reference, check that the cell reference is valid; if it is typed directly as text, use quotation marks.

A result that is a number but appears incorrect may be a match-mode or position misunderstanding rather than an error. MATCH returns a position counted from the first cell of its lookup range. If the match is in the fourth cell of a range beginning at row 10, MATCH returns 4, not 13. Also, exact text matching in MATCH is not case-sensitive: Apple and APPLE are treated as the same text for this purpose.

A practical troubleshooting sequence

Use this sequence before rebuilding a workbook or rewriting a lookup formula:

Read the error shown in the result cell and inspect the formula bar to see the full formula.
Set the match type to 0 if you need an exact match, and verify the lookup range is one row or one column.
Check the lookup value and the range for text-versus-number differences, spaces, punctuation, and hidden characters.
Confirm the range includes the expected cells and has not shifted after copying the formula.
Test a known value from the range. If it works, compare that value with the failing one to find a data difference.
If using 1 or -1, verify the required ascending or descending sort order and confirm an approximate result is appropriate.

For a quick diagnostic, select the lookup value and a candidate cell and compare their contents in the formula bar. You can also use Excel’s Evaluate Formula command from the Formulas tab to step through a formula and see which argument or intermediate result is causing trouble. On Windows, the shortcut is Alt+M, V. On Mac, use Formulas > Evaluate Formula if that command is available in the Excel version; ribbon keyboard shortcuts differ from Windows.

Using MATCH with INDEX

MATCH is often paired with INDEX when the goal is to return a related value rather than a position. Suppose A2:A6 contains Pear, Plum, Apple, Orange, and Kiwi, while B2:B6 contains 1.25, 0.90, 1.10, 1.40, and 0.75 in the corresponding rows. With Pear in D2, =INDEX(B2:B6,MATCH(D2,A2:A6,0)) returns 1.25. MATCH finds Pear’s position, 1, and INDEX returns the first value in the price range.

Keep the two ranges aligned: they should have the same number of rows (or columns) and represent corresponding records in the same order. If the lookup range begins at a different row from the return range, or one range has an extra cell, the position can select the wrong result. Exact matching in MATCH remains important here; otherwise INDEX may return a related value for an approximate position you did not intend.

When to use XMATCH or another approach

XMATCH is a newer alternative available in Microsoft 365 and Excel 2021 or later. It defaults to exact matching and offers options for search direction and wildcard matching, so it can make some lookup formulas clearer. For example, a reader using an older Excel release may not have XMATCH, so MATCH remains a useful compatibility choice. Excel for the web supports many modern functions, but feature availability can depend on the account and update channel.

For a simple task of finding a position in a list, MATCH is appropriate. If you need to return a value from another column, INDEX with MATCH works across many Excel versions. XLOOKUP, available in Microsoft 365 and Excel 2021 or later, can perform many lookup-and-return tasks directly and uses exact matching by default. Changing functions does not repair inconsistent source data, however; spaces, different data types, and incorrect ranges can affect any lookup method.

Frequently Asked Questions

Why does MATCH return #N/A when the value is visibly in the list?
Check for hidden spaces or characters, a number stored as text, an incorrect range, or a match type that does not fit the data. The displayed appearance of two cells does not guarantee their underlying values are identical.

Does MATCH return a row number or a position?
It returns a position relative to the first cell in the lookup array. A match in the third cell of the selected range returns 3, even if that cell is on worksheet row 20.

Is MATCH case-sensitive?
No. MATCH does not distinguish uppercase and lowercase text. If case-sensitive matching is required, a different approach using functions such as EXACT is needed.

Why does MATCH find the wrong item?
A missing third argument means approximate mode 1, which assumes ascending sort order. Specify 0 for an exact match, or verify that a deliberate approximate lookup uses the correct sort order.

Can MATCH search for part of a text value?
Yes. With match type 0, * can represent any number of characters and ? can represent one character. Use ~ before a wildcard character when you need to search for the character itself.

Does MATCH work in Excel for Mac and Excel for the web?
MATCH is available in current desktop Excel for Windows and Mac and in Excel for the web. The function syntax is the same, though some ribbon commands and keyboard shortcuts differ by platform.

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.