Excel MATCH Function Not Working With Numbers? Fixes

If Excel’s MATCH function cannot find a number, first check whether the lookup value and the numbers in the lookup range are the same type: a number stored as text is not necessarily an exact match for a real number. Use =MATCH(E2,B2:B6,0) for an exact match, and convert the text-number values to numbers if the types differ.

How MATCH searches for a number

MATCH looks for a value in a single row or column and returns its position within that range. It does not return the matched value itself. For example, if the match is the third item in B2:B6, MATCH returns 3, even though the match is in worksheet cell B4.

The function has three arguments: the value to find, the one-dimensional range to search, and an optional match type. For numeric searches, the third argument matters. Use 0 when you need an exact match. If you leave it out, Excel uses 1, which means an approximate match and assumes the lookup range is sorted in ascending order. That default can produce an unexpected position rather than an error.

With IDs in B2:B6 and the value to find in E2, an exact search is =MATCH(E2,B2:B6,0). The formula returns the position within B2:B6, or #N/A if it cannot find an exact match. The 0 is not the value being searched for; it tells MATCH to use exact-match behavior.

Check whether the values are numbers or text

A cell can display 103 and still contain text rather than the numeric value 103. This commonly happens when data is copied from a website, imported from a CSV file, or entered with a leading apostrophe. A cell’s appearance alone is not a dependable test: number formatting can make different underlying values look alike.

For example, suppose B2:B6 contains the text entries “101”, “102”, “103”, “104”, and “105”, while E2 contains the actual number 103. With that layout, =MATCH(E2,B2:B6,0) returns #N/A because the number in E2 does not exactly match the text entry in the range. The reverse mismatch can also happen: E2 may contain text “103” while the range contains the numeric value 103.

Select a suspicious cell and check the formula bar for a leading apostrophe or unexpected spaces.
Use Excel’s warning indicator, when it appears, to see whether the cell is flagged as a number stored as text.
Check alignment only as a clue: text often aligns left and numbers often align right by default, but formatting can change alignment.
Compare a known value from the lookup cell and the range rather than relying only on what is displayed.

Changing a cell’s number format from Text to General or Number does not necessarily convert text already stored in the cell. The underlying value must be converted or re-entered.

Convert text numbers in the lookup range

A helper column is a clear, dependable fix when the lookup range contains text versions of numbers. Assume B2:B6 contains text-number IDs and E2 contains the actual number 103. In C2, enter =VALUE(B2), then fill the formula down through C6. VALUE converts a numeric string such as “103” into the number 103. With the converted values in C2:C6, use =MATCH(E2,C2:C6,0); it returns 3 because 103 is the third item in that range.

In Microsoft 365 for Windows, fill the helper formula down by selecting C2 and dragging its fill handle, or copy it and paste it into C3:C6. On Mac, the same formula and fill-handle method work. A helper column also makes the conversion visible, which helps when you need to audit imported data or reuse the cleaned values in other formulas.

If the lookup cell contains text but the range contains real numbers, convert the lookup value instead. With numeric values in B2:B6 and text “103” in E2, use =MATCH(VALUE(E2),B2:B6,0). This returns 3. This approach only converts the lookup value; it will not fix a range where the entries themselves are stored as text.

Convert a whole column without a helper formula

For a column that should consistently contain numbers, Excel’s conversion tools can change the stored values in place. First make a copy of the data if preserving the original import matters. If Excel shows a warning icon beside the selected cells, use its Convert to Number option when available.

Another option is Text to Columns. Select the cells to convert, then choose Data > Text to Columns. In the wizard, choose Delimited and select Next, leave the delimiters unchecked if the values do not contain separators, select Next, set the column data format to General, and choose Finish. Check several results afterward: this operation changes how Excel interprets the selected data, and an unintended delimiter or format choice can alter imported content.

Do not convert identifiers to numbers automatically if leading zeros are meaningful. An account code such as 00123 may need to remain text; converting it to a number changes its stored value to 123. In that situation, make both the lookup value and lookup range consistent as text instead of forcing a numeric conversion.

Remove spaces and imported characters

A value can look numeric but contain leading or trailing spaces, a nonbreaking space, or another character copied along with it. Those characters can stop VALUE from converting the entry or prevent an exact match. Ordinary TRIM removes extra standard spaces, but it does not remove every character that can appear in copied data. A nonbreaking space, for example, has character code 160 in Excel.

With a possibly dirty numeric string in B2, a helper formula that replaces nonbreaking spaces with ordinary spaces, trims the result, and converts it is =VALUE(TRIM(SUBSTITUTE(B2,CHAR(160)," "))). Fill it down for the rest of the range, then match against the cleaned helper column. This formula assumes the remaining text is a number in a format Excel recognizes under the current regional settings. If the source contains labels, blank cells, or other nonnumeric entries, VALUE can return an error; inspect or handle those rows rather than assuming the entire column is clean.

Regional settings can also affect decimal and thousands separators. A string such as “1,25” may be interpreted differently depending on the system’s number conventions. If VALUE does not convert a legitimate number, check the source’s separators and the Excel or Windows regional settings before replacing punctuation. Blind replacements can turn a valid ID or decimal into a different value.

Use the correct match type and range

Even when the values are numeric and consistent, the wrong match type can be the cause. For an exact search, explicitly use 0. MATCH with 1, including the omitted-argument default, looks for the largest value less than or equal to the lookup value and requires ascending sort order. MATCH with -1 looks for the smallest value greater than or equal to the lookup value and requires descending sort order. If the data is unsorted, approximate matching can return an incorrect result.

Also verify that the lookup range is one row or one column and includes the expected cells. MATCH searches within the range you give it, not the entire worksheet. If the returned position is 3 for B2:B6, the matching cell is B4 because the result is relative to the first cell in the range. A common misunderstanding is to read 3 as worksheet row 3.

Duplicates are another important detail: an exact MATCH returns the position of the first matching item in the search direction. It does not report every duplicate. If the expected number appears more than once, check whether the first occurrence is the result you actually want.

Distinguish #N/A from other errors

#N/A usually means MATCH did not find an exact value in the specified range, but it does not identify why. Check data type, spaces, the search range, and the match type first. If the formula displays #VALUE!, #REF!, or another error, inspect the arguments and referenced cells: those errors point to a different problem than a simple missing exact match.

It is tempting to hide the error immediately with IFNA, but doing so can conceal a mismatch that should be fixed in the source data. If you do want a friendlier message after checking the cause, and the MATCH formula uses E2 and B2:B6, you can use =IFNA(MATCH(E2,B2:B6,0),"Not found"). This returns the position when a match exists and “Not found” when MATCH returns #N/A. It does not convert text to numbers or correct the range.

MATCH compared with XLOOKUP and XMATCH

MATCH remains useful when another formula needs a position, such as INDEX, or when a workbook must work with older Excel versions. XLOOKUP is often more direct when the goal is to return a value from a corresponding range. XMATCH is a newer position-search function with exact matching as its default. XLOOKUP and XMATCH are available in Microsoft 365 and Excel 2021 or later.

Switching functions does not automatically solve a number-versus-text mismatch. If one side contains text “103” and the other contains numeric 103, normalize the data or convert the relevant value first. The choice of function changes search behavior and what is returned; it does not make inconsistent source data equivalent.

Frequently Asked Questions

Why does MATCH return #N/A when both cells show the same number?
One value may be a number and the other text, or one may contain an invisible space or imported character. Check the underlying data and convert or clean the values before using an exact match.

Does MATCH default to an exact match?
No. If you omit the third argument, MATCH uses 1, which performs an approximate match and assumes ascending sort order. Use 0 explicitly for an exact match.

Does MATCH return the worksheet row number?
No. It returns the item’s position within the lookup range. If the range is B2:B6 and the result is 3, the matching cell is B4.

Can I fix a number stored as text by changing the cell format?
Usually not. Changing Text to General or Number changes the display format, but does not necessarily convert an existing text value. Use VALUE, Text to Columns, or Excel’s Convert to Number option.

Why does MATCH find the wrong number without returning #N/A?
Check whether the formula omits the match-type argument or uses 1 or -1. Approximate matching depends on sort order; use 0 when you want an exact 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.