Excel MATCH Function Not Working? Common Fixes

If Excel’s MATCH function is not returning the result you expect, first use an exact-match formula such as =MATCH(E2,A2:A100,0) and check that the lookup value in E2 is genuinely the same as a value in A2:A100. The most common causes are an incorrect match type, differences in data or spacing, and a lookup range that does not contain the value.

What MATCH does—and what its result means

MATCH searches a single row or column for a value and returns that value’s position within the specified range. It does not return the matched value itself, and its position is relative to the range rather than necessarily the worksheet row number. This distinction often explains a result that looks wrong even though the formula is working.

For example, with values 104, 208, 311, and 415 in A2:A5 and 311 in E2, the formula =MATCH(E2,A2:A5,0) returns 3. That means 311 is the third item in A2:A5; its worksheet row is 4. MATCH is often combined with INDEX when you want to retrieve a corresponding value from another column.

The function’s basic syntax is =MATCH(lookup_value,lookup_array,match_type). The lookup array must be a single row or column, not a rectangular block containing multiple rows and columns. The match type controls what MATCH considers a match, so it is important to specify it deliberately.

Check the match type first

For a normal lookup that should find an identical item, use match type 0. If you omit the third argument, Excel uses 1—not exact matching. That default is a frequent source of surprising positions and #N/A errors.

0: Finds an exact match. The values do not need to be sorted. For text, MATCH is not case-sensitive, and match type 0 also supports wildcards.
1 or omitted: Finds the largest value less than or equal to the lookup value. The lookup array must be sorted in ascending order for the result to be reliable.
-1: Finds the smallest value greater than or equal to the lookup value. The lookup array must be sorted in descending order.

Approximate matching is useful for tasks such as finding a tax band or commission tier, but it is risky on an unsorted list. For instance, if a rate table is meant to use match type 1 and its thresholds are out of order, MATCH may return an unexpected position rather than clearly identifying the sorting problem. If you want exact matching, use 0 even when the data happens to be sorted.

Fix #N/A when the value appears to be present

#N/A means MATCH could not find an acceptable match under the specified rules. A value can look identical on screen while differing underneath. Compare the lookup cell and candidate value in the formula bar, and check for these common differences:

Leading or trailing spaces: “North” and “North ” can behave as different text values.
Nonbreaking spaces or imported characters: Data copied from a website or another system may contain character codes that ordinary spaces do not reveal.
Numbers stored as text: A cell containing text that looks like 123 may not be normalized in the same way as a numeric 123. Check the cells’ underlying values and formats, and convert the source data consistently rather than relying on appearance.
Dates or times with different underlying values: Dates are stored as serial numbers, and a value that includes a time may not equal a date-only value.
Misspellings or punctuation: Hyphens, apostrophes, double spaces, and other small differences count in an exact text lookup.

For a quick diagnosis, use Excel’s Find command to search the lookup array for the value, but do not treat a visual match as proof that the underlying values are identical. You can also compare the suspected cells with an equality test. With the lookup value in E2 and a candidate in A7, enter =E2=A7 in another cell; TRUE indicates Excel considers the two values equal in that comparison, while FALSE points to a difference worth investigating.

Remove unwanted spaces and imported characters

If imported text contains ordinary extra spaces or nonprinting characters, a helper column can clean the source before you run MATCH. With the uncleaned text in A2, enter =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) in B2 and fill it down. TRIM removes extra ordinary spaces at the beginning and end and reduces repeated ordinary spaces between words; CLEAN removes many nonprinting characters; SUBSTITUTE replaces the nonbreaking space represented by CHAR(160). The resulting text for a value such as “ North ” is “North.”

Cleaning both the lookup value and the list consistently is more reliable than cleaning only one side. Keep the original data if it may be needed for auditing, and remember that CLEAN does not remove every possible Unicode character. If the helper column still does not match, inspect the characters or ask for a clean export from the source system. Avoid TRIM if repeated spaces are meaningful in your data, such as in a precisely formatted identifier.

Understand wildcard and case behavior

With match type 0, MATCH can use two wildcards in text searches: an asterisk (*) represents any number of characters, and a question mark (?) represents one character. For example, if E2 contains the word “son” and A2:A100 contains product names, =MATCH("*"&E2&"*",A2:A100,0) returns the position of the first item containing “son.” It does not list every match; MATCH returns one position.

To search for a literal asterisk or question mark rather than use it as a wildcard, put a tilde (~) before that character in the lookup text. Because standard MATCH text matching is case-insensitive, it cannot by itself distinguish “Maple” from “MAPLE.” If capitalization is meaningful, use a case-sensitive approach such as a formula built with EXACT and a position search, or add a helper column that explicitly identifies the case-sensitive condition. Do not assume that changing letter case alone will make MATCH choose a different result.

Check the range, references, and formula entry

Confirm that the lookup array is the intended one-dimensional range and includes the cells where the value could occur. A reference such as A2:A100 searches that column only; it will not find a value stored in column B. Also check that the lookup value is not accidentally outside the expected range because new rows were added below it.

Relative references move when a formula is copied. If you copy a formula down and need every row to search the same list, lock the lookup array with dollar signs. With the lookup value in E2 and the fixed list in A2:A100, enter =MATCH(E2,$A$2:$A$100,0) and fill down. The result is the position of E2 in the fixed list, while E2 changes to E3, E4, and so on in subsequent rows.

If Excel displays the formula text instead of calculating it, make sure the cell is not formatted as Text, remove any leading apostrophe, and re-enter the formula. Also check whether Show Formulas is enabled; on Windows, Ctrl+` toggles that display. If results are stale, check Formulas > Calculation Options > Automatic. Excel for Mac has equivalent formula-display and calculation controls, though keyboard shortcuts can differ. In Excel for the web, the ribbon layout may differ from the Windows desktop app.

Know what other errors can indicate

#VALUE! can point to an invalid argument or a problem with the values supplied to the function; check that the lookup array is a valid single row or column and that the formula arguments are in the intended order. A #REF! error usually means a referenced range has been deleted or otherwise made invalid. If the formula returns a number but selects the wrong item, check the match type and whether you expected a worksheet row number instead of a position within the range.

To show a friendlier message when an exact value is absent, with the search value in E2 and the list in A2:A100, use =IFNA(MATCH(E2,A2:A100,0),"No match"). If E2 contains 311 and 311 is the third item in the list, the result is 3; if it is absent, the result is “No match.” IFNA hides only #N/A, which is generally preferable to IFERROR when you want other formula problems to remain visible for diagnosis.

When XMATCH or another lookup is a better fit

XMATCH is a newer alternative available in Microsoft 365 and Excel 2021 or later. It uses exact matching by default and offers additional search options, which can make a lookup easier to read. With the lookup value in E2 and the list in A2:A100, =XMATCH(E2,A2:A100) returns the exact position when E2 is found. If you need to support an older Excel version, MATCH is more broadly compatible.

Neither function returns the related value from another column on its own. If you need that value, XLOOKUP can return it directly in Microsoft 365 and Excel 2021 or later; in older workbooks, INDEX combined with MATCH is a common approach. Choose based on the workbook’s compatibility needs and whether you need a position or a returned value. Switching functions will not fix inconsistent source data, so normalize values first when the real cause is a text, spacing, or date mismatch.

Frequently Asked Questions

Why does MATCH return 1 when my value is not the first item?
Check whether the third argument is missing or set to 1. That requests approximate matching and assumes the list is sorted ascending. Add 0 for an exact match, or sort the data correctly if approximate matching is intentional.

Why does MATCH return #N/A even though I can see the value?
The displayed text may conceal a trailing space, nonbreaking space, text-versus-number difference, or other character mismatch. Use exact match type 0, inspect the underlying values, and clean both the lookup value and the list consistently.

Does MATCH distinguish uppercase and lowercase letters?
No. MATCH is case-insensitive for text. Use a case-sensitive formula approach involving EXACT if case must determine which item qualifies.

Does MATCH return the row number?
No. It returns the item’s position within the lookup array. If the array begins at A2 and MATCH returns 3, the matching cell is A4, not worksheet row 3.

Can MATCH search a two-dimensional table?
No. Its lookup_array must be one row or one column. Search a specific row or column, or use a different lookup method when the task requires a two-dimensional search.

Should I use MATCH or XMATCH?
Use MATCH when compatibility with older Excel versions matters. XMATCH, available in Microsoft 365 and Excel 2021 or later, defaults to exact matching and provides extra search options, but neither function fixes mismatched source data.

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.