Excel MATCH Not Working? Fix #N/A and Wrong Results

If Excel’s MATCH function returns #N/A or the wrong position, first check its third argument: use 0 for an exact match. With the value to find in E2 and the search list in A2:A10, enter =MATCH(E2,A2:A10,0); if E2 appears fourth in that range, the result is 4.

What MATCH returns—and why the result may surprise you

MATCH locates a value in one row or one column and returns its relative position in that search range. It does not return the matching cell’s contents. If the range is A2:A10 and the match is in A5, MATCH returns 4, because A5 is the fourth cell within A2:A10. The position is relative to the range, not the worksheet row number.

This distinction matters when MATCH is used by itself or paired with another function. It is common to use it with INDEX: MATCH finds which item to use, and INDEX returns the corresponding result from another range. The lookup and return ranges must line up. If the search starts in A2 but the return range starts in B3, the same position points to different records and can produce a plausible but incorrect answer.

MATCH has three arguments: the value to find, the one-dimensional range to search, and an optional match type. That last argument is a frequent source of trouble because omitting it does not request an exact match. In Excel, the default is approximate matching with match type 1.

Check match_type before changing the data

For a normal lookup—such as finding an employee ID, product name, or invoice number—specify 0. Exact match does not require the search list to be sorted. If there are duplicates, MATCH returns the position of the first matching item it encounters.

Match types 1 and -1 are approximate-match modes, and their sorting requirements are important:

0: Finds an exact match. The list can be in any order.
1, or omitted: Assumes the list is sorted in ascending order and finds the largest value that is less than or equal to the lookup value.
-1: Assumes the list is sorted in descending order and finds the smallest value that is greater than or equal to the lookup value.

With numbers 20, 40, 60, and 80 in A2:A5, and a lookup value of 68 in E2, this formula uses ascending approximate matching:

With E2 set to 68 and A2:A5 containing 20, 40, 60, 80 in that order, =MATCH(E2,A2:A5,1) returns 3, the position of 60.

With values 100, 80, 60, and 40 in A2:A5 and E2 set to 70, =MATCH(E2,A2:A5,-1) returns 2, the position of 80. The example illustrates why an approximate result is not necessarily the same as an exact lookup. If a list is unsorted, approximate matching can return an unexpected position; do not try to fix an exact-lookup problem by sorting unless approximate matching is genuinely what you need.

Diagnose #N/A with a focused checklist

#N/A usually means Excel did not find a value that satisfies the lookup rules. Before adding error-handling formulas, compare the lookup value with the cells in the range and confirm the match type. Work through these checks:

Confirm the lookup value is present. Check spelling, punctuation, leading or trailing spaces, and whether the selected range includes the expected row. A value just outside the referenced range cannot be found.
Check the third argument. For a standard exact lookup, use 0. An omitted argument may invoke approximate matching and can behave unpredictably if the list is not sorted as required.
Check the stored values, not just their appearance. A cell formatted to display a number with leading zeroes may contain a number, while a similar-looking cell may contain text. Formatting changes how a value looks; it does not necessarily change the underlying value.
Inspect spaces and invisible characters. Copied data may include ordinary spaces or nonbreaking spaces. These characters can make text that looks identical compare differently.
Check for formula-generated text. A cell may display a label but contain a formula that returns an empty string or a slightly different result. Select the cell and inspect the formula bar.
Check whether the range moved. If you copied a formula, relative references may have shifted. Use dollar signs to lock a range when it should stay fixed as the formula is copied.

Text matching with match type 0 is not case-sensitive: for example, “north” and “North” are treated as the same text for this purpose. However, MATCH supports the wildcard characters * and ? in exact-match mode: * stands for any number of characters, and ? stands for one character. If a lookup value contains a literal wildcard, precede it with a tilde (~) to search for the character itself.

Fix numbers stored as text and unwanted spaces

When values come from a CSV export, web page, or another system, cells that look alike may have different underlying types. Excel’s handling of comparisons can depend on the values and how they were imported, so inspect suspect cells rather than assuming the display proves they are identical. With a sample value in A2, =ISNUMBER(A2) returns TRUE if it is stored as a number; =ISTEXT(A2) returns TRUE if it is text. These checks help identify a mismatch before you convert a whole column.

To normalize text that has extra ordinary spaces, create a helper column and clean both the lookup value and the search values consistently. TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. It does not remove every invisible character that can arrive with imported data.

With the original text in A2, enter =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) in a helper cell and fill it down the data. This replaces nonbreaking spaces with ordinary spaces and then trims the result. Apply equivalent cleanup to the lookup value in a helper cell, then run MATCH against the cleaned search column. For example, if cleaned search values are in C2:C10 and the cleaned lookup is in F2, =MATCH(F2,C2:C10,0) returns the position within C2:C10, not the original column’s worksheet row number.

For numbers stored as text, choose a conversion method appropriate to the data. Excel’s Data tab includes Text to Columns, which can convert many imported text values into numbers; multiplying by 1 in a helper column is another common conversion for values that are valid numeric text. Do not convert identifiers such as postal codes or product codes automatically: leading zeroes may be meaningful, and those values are often better treated consistently as text.

Make the lookup and return ranges line up

If MATCH returns a position but the result pulled from another column is wrong, inspect the ranges in the formula that uses it. Both ranges should cover the same records in the same order. For example, if names are in A2:A10 and departments are in B2:B10, the first name in the lookup range corresponds to the first department in the return range.

With the name to find in E2, names in A2:A10, and corresponding departments in B2:B10, =INDEX(B2:B10,MATCH(E2,A2:A10,0)) returns the department on the matching row. If the name is in A5 and its department is in B5, MATCH returns 4 and INDEX returns the fourth item in B2:B10, which is B5. This is a useful pattern when a lookup should return data from a column to the left or right of the search column.

For formulas copied down a report, lock the lookup ranges if they must remain fixed. With the lookup value in E2 and a fixed list in A2:A10, =MATCH(E2,$A$2:$A$10,0) keeps the search range in place when copied to another row. The dollar signs do not change the match rules; they prevent the reference from shifting.

Use error handling only after finding the cause

Once you know an unmatched value is an expected possibility, you can display a clearer message instead of showing #N/A. Error handling is useful in a finished report, but it can conceal a misspelled value, a broken reference, or a data-quality problem while you are troubleshooting. Test the plain MATCH formula first.

With the lookup value in E2 and the exact-match list in A2:A10, =IFERROR(MATCH(E2,A2:A10,0),"Not found") returns the position if there is a match and the text “Not found” if the formula produces an error. IFERROR catches errors beyond a missing match, so use it thoughtfully; a broad message may hide an error you would otherwise want to investigate.

A related alternative is XMATCH, available in Microsoft 365 and Excel 2021 or later. XMATCH defaults to exact matching, so the third argument is not needed for the ordinary exact-lookup case. It also has options for wildcard matching and searching from the last item toward the first. MATCH remains useful for compatibility with older workbooks and for formulas that already use its position result.

With the lookup value in E2 and the search list in A2:A10, =XMATCH(E2,A2:A10) returns the exact position by default. To search from the bottom of that range upward, use =XMATCH(E2,A2:A10,0,-1); if the value occurs more than once, this returns the position of the last occurrence. XMATCH also returns a position within the supplied range, not a worksheet row number.

Frequently Asked Questions

Why does MATCH return 1 when the value is not an exact match?
If the third argument is omitted or set to 1, MATCH performs ascending approximate matching. It may return the position of the largest value that does not exceed the lookup value. Specify 0 for an exact match, and use an appropriately sorted list only when approximate matching is intended.

Does MATCH distinguish between uppercase and lowercase?
No. MATCH is not case-sensitive, so text that differs only by capitalization is treated as a match. If capitalization must be distinguished, use a case-sensitive approach such as combining EXACT with an array-capable formula; the ordinary MATCH function alone does not enforce case.

Why does MATCH return a position instead of a row number?
Its answer is relative to the range you supplied. If the fourth cell in A2:A10 matches, MATCH returns 4, even though that cell is on worksheet row 5. To return associated data, use the position with INDEX and a corresponding return range.

Can MATCH find a value when it appears more than once?
Yes, but ordinary MATCH returns the first matching item in the search direction. In Microsoft 365 or Excel 2021 and later, XMATCH can search from last to first when its search mode is set to -1, which is useful when the last occurrence is needed.

What does #VALUE! mean in a MATCH formula?
Check that the match type is 1, 0, or -1. A match type outside those supported values can produce #VALUE!. Also check that the arguments are valid and that the lookup range is a single row or column.

Should I replace MATCH with XMATCH?
Not necessarily. XMATCH is convenient in newer Excel versions because exact matching is its default and it offers reverse searches. MATCH is widely compatible and works well when the formula specifies 0 for exact matching. Choose based on workbook compatibility and the search behavior you need.

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.