MATCH Not Working in Excel? Fix Common Formula Errors

If MATCH is not working in Excel, first check the third argument: use 0 for an exact match, and make sure the lookup value and lookup range contain compatible values. A typical exact-match formula is =MATCH(E2,A2:A10,0); it returns the item’s position within A2:A10, not its worksheet row number.

What the MATCH function does

MATCH searches a single row or column for a value and returns its relative position in that range. It does not return the matching value itself. That distinction explains many results that look wrong at first glance, especially when a range starts below row 1.

For example, with the names Ava, Ben, Cara, Diego, and Eli in A2:A6, and Cara in E2, the exact-match formula below returns 3. Cara is the third item in A2:A6, even though it is on worksheet row 4.

Assuming names are in A2:A6 and the search name is in E2, enter: =MATCH(E2,A2:A6,0). With Cara in E2, the result is 3.

The syntax is MATCH(lookup_value, lookup_array, [match_type]). The lookup array must be one-dimensional: a single row or a single column. The optional match_type controls how Excel searches. It is not a choice between “more accurate” and “less accurate”; it changes the matching rules.

0 searches for an exact match and does not require the lookup range to be sorted.
1, or an omitted match_type, finds an approximate match in a range sorted in ascending order.
-1 finds an approximate match in a range sorted in descending order.

Use the right match_type

The default is a frequent cause of trouble. If you leave out the third argument, Excel uses 1. That is appropriate for a sorted threshold table, but it can return an unexpected position when you want an exact text or ID match. For ordinary lookups, explicitly include 0.

With product codes in A2:A6 and the requested code in E2, use this exact-match formula: =MATCH(E2,A2:A6,0). If the code occurs fourth in the range, the result is 4, regardless of whether A2:A6 is sorted.

Approximate matching is useful for bands such as commission rates or grading thresholds. Suppose the ascending lower limits 0, 50, 100, and 200 are in A2:A5. A lookup value of 135 matches the largest limit that is less than or equal to 135: 100, which is in position 3. The range must be sorted ascending for match_type 1; unsorted values can produce misleading results.

For match_type -1, the values must be sorted descending. Excel returns the position of the smallest value that is greater than or equal to the lookup value. If the range is not in the required order, do not rely on approximate MATCH. Use exact matching or sort the data appropriately.

Why MATCH returns #N/A

#N/A means Excel did not find a match under the specified rules. It does not necessarily mean the visible text is obviously different. Check the value, the range, and the data types before changing the formula.

Wrong match_type: Add 0 for an exact search. An omitted argument defaults to approximate matching.
Text versus number: A number stored as text, such as text “123”, may not match the numeric value 123. Check whether the source cells are genuinely numbers or text.
Extra spaces: Leading or trailing spaces can make text values differ even when they look identical. Imported data may also include nonbreaking spaces that ordinary trimming does not remove.
Different characters: A hyphen, apostrophe, or accented character may be a different character from the one in the lookup value. Copying a value directly from the source can help identify this.
Incorrect range: Confirm that the lookup array includes the expected cells and is one row or one column, not a two-dimensional block.
Formula result: A cell that looks like a label may contain a formula returning text, while the lookup value is a number, or vice versa.

To check whether a cell is stored as a number, select it and inspect its number format and formula bar; formatting alone does not convert text into a numeric value. If you need to clean imported data, test any conversion on a copy first. Applying TRIM to the lookup value alone will not remove spaces from every item in the lookup range.

Check spaces, wildcards, and duplicate values

With match_type 0, MATCH can use wildcards when the lookup value is text: an asterisk represents any number of characters, and a question mark represents one character. Put a tilde before a wildcard character when you need to search for a literal asterisk or question mark. Wildcards are not a way to search for partial numbers.

For example, if A2:A10 contains customer names and E2 contains Ann, this formula can find the first name beginning with Ann, such as Ann Lee. Assuming those cell locations, use: =MATCH(E2&"*",A2:A10,0). The result is the position of the first qualifying name in the range, not the text of the name. If you want the entire cell to equal Ann, do not add the asterisk.

MATCH is not case-sensitive: searching for “alex” can match “Alex.” It also returns the position of the first qualifying occurrence when a value appears more than once. If duplicates exist, the result may be correct but not identify the occurrence you intended. Check the source list or use a more specific lookup value if each record needs to be distinguished.

Understand the returned position and range

A position is counted from the first cell in lookup_array. For a range beginning at A2, position 1 means A2; it does not mean worksheet row 1. This is not an error. It is the function’s intended output and is useful when another function needs a relative position.

Make sure the lookup range is the same one you intend to use elsewhere. If MATCH searches A2:A20 but another formula uses B3:B21, the positions will be offset by one row. When combining MATCH with INDEX, align the lookup and return ranges so their first and last rows correspond.

For example, suppose names are in A2:A10, sales amounts in B2:B10, and the name to find is in E2. To return the corresponding sales amount, use: =INDEX(B2:B10,MATCH(E2,A2:A10,0)). If the requested name is on the fourth position in A2:A10, INDEX returns the fourth item from B2:B10. The formula works because both ranges begin on row 2 and have the same size.

Fix formula entry and calculation problems

If the formula itself appears in the cell instead of a result, check whether the cell is formatted as Text. Change the format to General, then re-enter the formula; changing the format alone may not cause an already-entered formula to calculate. Also check for an apostrophe before the equals sign or a missing equals sign.

If the result does not update after changing a lookup value, confirm that Excel is calculating automatically. On Windows, open Formulas > Calculation Options and select Automatic if appropriate. Manual calculation can leave formulas showing previous results until recalculation. Press F9 to recalculate open workbooks; on some laptops you may need the Fn key with the function key.

When a formula returns an error, select the cell and use Formulas > Evaluate Formula to step through the calculation. This can reveal whether the lookup value, range, or match_type is different from what you expect. Also inspect the formula bar: a visually similar cell reference may point to another sheet or to the wrong range.

Handle missing matches without hiding useful errors

Sometimes a missing match is an expected outcome, such as when a new code has not yet been added to a reference list. In that case, you can show a clearer message instead of #N/A. Assuming codes are in A2:A20 and the requested code is in E2, use: =IFNA(MATCH(E2,A2:A20,0),"Not found"). If there is no exact match, the formula displays Not found; otherwise, it returns the position.

IFNA handles #N/A specifically, so it can leave other problems visible. That is generally preferable to replacing every possible error with a message, because errors such as an invalid reference may signal a separate formula issue. Do not use error handling to disguise a wrong range or a mistaken match_type; fix the underlying cause first.

MATCH and XMATCH: which should you use?

MATCH remains useful in workbooks that need compatibility with older Excel versions and in established INDEX/MATCH formulas. XMATCH, available in Microsoft 365 and Excel 2021 or later, is a newer alternative with exact match as its default and additional search options. It can search from the first item or from the last item, which is helpful when the last duplicate is needed.

For a straightforward exact lookup, either function can return a position. If you use MATCH, specify 0 so the intended rule is clear. If you choose XMATCH, remember that colleagues using older Excel releases may not be able to calculate it. The function choice does not change the core idea: both return a position, so pair either with INDEX when you need a value from a corresponding range.

Frequently Asked Questions

Why does MATCH return the wrong number?
It may be returning a relative position rather than a worksheet row number, or it may be using approximate matching because match_type is 1 or omitted. Count from the first cell in the lookup range and use 0 when you need an exact match.

Does MATCH distinguish uppercase and lowercase?
No. MATCH is not case-sensitive. If capitalization must distinguish otherwise identical entries, MATCH alone is not sufficient; use a case-sensitive approach designed for that requirement.

Can MATCH find part of a text value?
Yes, for text searches with match_type 0, you can use * for any number of characters or ? for one character. For example, a search value ending in an asterisk can match text beginning with that value. Remember that MATCH returns the position, not the matching text.

Why does MATCH work for some numbers but not others?
Some cells may contain numbers stored as text while others contain numeric values. Imported data can also contain spaces or inconsistent formatting. Compare the underlying values and normalize the data types before relying on the search.

Can MATCH search across multiple columns?
No. Its lookup_array must be a single row or a single column. Choose one column to search, or build a separate helper column if the key is spread across multiple fields.

How do I return the matching value instead of a position?
Use INDEX with MATCH: MATCH finds the relative position, and INDEX returns the item at that position from a corresponding range. Keep the lookup and return ranges aligned and the same size to avoid an offset result.

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.