Excel MATCH Not Working? Fix Common Lookup Errors

If Excel MATCH is returning the wrong position or #N/A, first use exact-match mode by adding 0 as its third argument, then check that the lookup value and lookup range contain the same kind of data and no hidden spaces. MATCH returns a position within its lookup range—not the matched value—so the range, match type, and expected result all matter.

What MATCH does and what its result means

MATCH searches a one-dimensional range or array for a value and returns its relative position. It does not return a value from a neighboring column. If the match is second in the selected range, MATCH returns 2, even if that range begins in worksheet row 10. This distinction explains many reports that MATCH is “wrong”: the result is an index within the range, not a worksheet row number.

For example, with the lookup value P-104 in A2 and P-101, P-104, P-108, and P-110 in D2:D5, MATCH returns 2. The exact-match formula for those cells is =MATCH(A2,$D$2:$D$5,0). The dollar signs keep the lookup range fixed if you copy the formula down. If you need the corresponding result from another column, combine MATCH with INDEX or use a lookup function that returns a value.

MATCH’s arguments are the value to find, the one-dimensional lookup range, and an optional match type. The optional argument is important: leaving it out does not mean exact match. Excel uses match type 1 by default, which looks for an approximate match in data sorted in ascending order.

Set the right match type

Choose the third argument according to the job, rather than relying on the default:

0 finds the first exact match and does not require the lookup range to be sorted. This is usually the safest choice for product codes, names, and other identifiers.
1, or an omitted argument, finds the largest value less than or equal to the lookup value. The lookup range must be sorted in ascending order.
-1 finds the smallest value greater than or equal to the lookup value. The lookup range must be sorted in descending order.

Approximate matching is useful for thresholds, such as assigning a rate based on a score band, but unsorted data can produce a plausible-looking yet incorrect position. For a list of customer names, an exact lookup is generally intended; include the third argument as zero. Exact text matching with match type 0 is not case-sensitive, and it can interpret the asterisk and question mark as wildcards. If those characters are part of a literal identifier, escape them with a tilde.

Check the lookup value and data types

Two cells can look identical on screen but contain different values. A number stored as text, for instance, may not match the numeric value with the same digits. This commonly happens when data is imported from a CSV, copied from a website, or entered with a leading apostrophe. Dates can also be involved: Excel stores real dates as serial numbers, while a date-looking text string is still text.

Check the source cells and, if needed, use a temporary test cell to inspect or convert the data consistently. For a numeric identifier, decide whether leading zeros are meaningful. If 00125 is a code, it should usually remain text; converting it to the number 125 changes the identifier. If the values represent quantities, converting both sides to numbers may be appropriate. Avoid changing only the lookup value or only the lookup column.

Leading, trailing, or repeated spaces are another frequent cause of #N/A. The TRIM function removes ordinary extra spaces, but copied web data can contain nonbreaking spaces that TRIM alone does not remove. With the text to clean in A2, this formula replaces nonbreaking spaces with ordinary spaces and then trims them: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). If A2 contains the text Acme with an extra nonbreaking space at the end, the result is Acme. Clean a helper column on both sides of the comparison where necessary; do not assume the visible text tells the whole story.

Diagnose common errors and unexpected positions

#N/A means MATCH did not find a qualifying item. It does not necessarily mean the item is visibly absent: check text-versus-number differences, spaces, punctuation, spelling, and whether the formula includes the row containing the item. A range that stops at D20 cannot find a value in D21. Also confirm that the lookup value refers to the intended cell and that the selected range is one row or one column.

A surprising number instead of #N/A often points to an omitted or incorrect match type. With match type 1, an unsorted list can give a misleading result; with match type -1, the sort direction must be descending. With match type 0, repeated values return the position of the first matching item. If you need to distinguish repeated records, add another criterion or create a unique key rather than expecting MATCH to select a later duplicate.

Check the formula’s endpoints as well. If the lookup array is D2:D20 and a returned position is 5, the match is the fifth cell in that range—D6—not necessarily row 5. A range accidentally shifted by one row can therefore make an otherwise valid result look incorrect. When copying a formula, use absolute references for ranges that should not move, or review the references in each copied row.

To return a readable message instead of an error, wrap an exact MATCH in IFNA. With the lookup value in A2 and the list in D2:D20, the formula is =IFNA(MATCH(A2,$D$2:$D$20,0),"Not found"). It returns the position when there is a match and Not found when there is no exact match. This makes a report easier to read, but it does not repair a data mismatch; keep a way to investigate missing records.

Make MATCH return the related value

If the goal is to find a price, department, or other detail rather than a position, pair MATCH with INDEX. Suppose A2 contains a product code, D2:D20 contains product codes, and E2:E20 contains their prices. The formula =INDEX($E$2:$E$20,MATCH(A2,$D$2:$D$20,0)) returns the price on the row where the exact code is found. If P-104 is the second code in D2:D20 and its corresponding price in E3 is 12.5, the result is 12.5. The lookup and return ranges must cover corresponding rows in the same order.

This combination is useful when a workbook needs to support older Excel versions or when you want the position-finding and value-returning steps to be explicit. If the code cannot be found, the combined formula returns an error unless you handle it, for example with IFNA. Keep the lookup range and return range aligned; ranges of different lengths or offsets can return an unrelated value even when MATCH itself found the right position.

When to use XLOOKUP or XMATCH instead

In Microsoft 365, XLOOKUP can search for a value and return a corresponding value directly, while XMATCH returns a position with options for exact and approximate matching. These functions are available in Microsoft 365 and Excel 2021; they are not available in every older Excel edition. XLOOKUP uses exact match by default, which avoids MATCH’s easy-to-miss default approximate behavior. MATCH remains useful for compatibility, for combining with INDEX, and for formulas that specifically need a relative position.

Do not switch functions without checking the workbook’s compatibility needs. A file shared with someone using an older Excel release may show an unsupported-function error for newer functions. Whichever function you use, the underlying data still needs consistent types, clean values, and suitable search settings. Replacing MATCH will not make a number stored as text equal to a numeric value or remove an invisible character.

A practical troubleshooting order

Work through these checks from the simplest cause to the less obvious ones. Test with a small, known example before changing a large report:

Confirm the lookup value and the exact cells in the lookup array. Check for a missing last row or a shifted range.
For a normal exact lookup, set the third argument to 0. Do not leave it blank by habit.
Compare the underlying types: text against text, or numbers against numbers. Treat codes with meaningful leading zeros as text.
Look for leading or trailing spaces, nonbreaking spaces, punctuation, and inconsistent spelling. Clean the source data where practical.
If using approximate matching, sort the array in the required direction and verify that approximate behavior is actually intended.
Check for duplicates and remember that exact MATCH returns the first one it encounters.
Verify whether the formula should return a relative position or a related value from another column.

For a large or frequently refreshed workbook, it is usually better to fix the imported data once in a source or helper column than to build increasingly complicated cleanup into every lookup formula. Keep a record of any transformation, especially where an identifier may contain leading zeros or meaningful spaces. A tidy source also helps other formulas, filters, and pivot tables behave consistently.

Frequently Asked Questions

Why does MATCH return the wrong number?
It may be using approximate matching because the third argument is omitted, or it may be returning the position within the selected range rather than a worksheet row number. Use match type 0 for an exact lookup and check the range’s starting cell.

Does MATCH default to exact match?
No. If you omit the third argument, MATCH uses match type 1, which expects ascending-sorted data and performs an approximate match. Add 0 when you want an exact match.

Why does MATCH show #N/A when the value is clearly there?
The visible value may differ internally because of text-versus-number formatting, hidden spaces, nonbreaking spaces, or a range that excludes the matching cell. Compare the underlying data and verify the full lookup range.

Can MATCH distinguish uppercase and lowercase?
No. MATCH’s text comparison is not case-sensitive. If capitalization must distinguish records, use a case-sensitive approach based on EXACT rather than relying on MATCH alone.

How do I get the value next to the match?
Use INDEX with MATCH, making sure the return range corresponds row for row with the lookup range. In Microsoft 365 or Excel 2021, XLOOKUP is another option that can return a related value directly.

Why does MATCH return the first duplicate?
That is its normal behavior: it returns the position of the first matching item in the search order. Create a unique key or add another condition if the records need to be identified separately.

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.