XLOOKUP Not Working With Numbers? Fix the Match

Errors & fixes · Updated November 3, 2026

If XLOOKUP is not finding a number, first check whether the lookup value and lookup column contain the same kind of data: a real number may not match a number stored as text. In Microsoft 365 for Windows, you can test a text-formatted lookup value with =XLOOKUP(VALUE(E2),A2:A5,B2:B5,"Not found"); this works when E2 contains numeric text and A2:A5 contains real numbers.

Why XLOOKUP can miss a number

Excel stores numbers and text differently, even when both appear on the screen as 102. A cell may contain the number 102, or it may contain the text string “102”. XLOOKUP’s default is an exact match, so an apparent match can fail when the underlying values are different types. Formatting a cell to look like a number does not convert its stored value.

This often happens when data comes from a CSV, a website, a database export, or a system that treats codes as text. A green triangle in a cell’s corner can indicate a number stored as text, but its absence does not prove that every value in the column has the same type. A range can even contain a mixture of numeric values and numeric text.

In the opening example, E2 contains the text “102”, while A2:A5 contains numbers 101, 102, 103, and 104 and B2:B5 contains names. VALUE converts E2 to a number; XLOOKUP then finds 102 and returns the corresponding value from B2:B5. The result is the name on the row containing 102.

Check the value and the lookup range

Before changing a formula, confirm what is stored in the lookup cell and in a representative cell in the lookup column. The ISNUMBER and ISTEXT functions can help identify a type mismatch. With the value to check in A2, =ISNUMBER(A2) returns TRUE for a real number and FALSE for text. With the same layout, =ISTEXT(A2) returns TRUE if A2 contains text.

Also check that the formula points to the intended cells. XLOOKUP uses this order: lookup value, lookup array, return array, optional value to return when no match is found, optional match mode, and optional search mode. The lookup array and return array must line up: the item XLOOKUP finds in one must correspond to the result in the other. A shifted range can produce a wrong answer even if the number itself is valid.

If you include a not-found message, such as “Not found”, XLOOKUP displays that message instead of #N/A when there is no match. That makes a type mismatch less obvious, so temporarily remove the optional message or inspect the source values if the result seems unexpected. A message is useful for a finished worksheet, but it does not explain why a match failed.

Convert numeric text when it represents a number

If the lookup column should contain numbers, convert its text values to real numbers so the data is consistent. For a small table, select the cells with the warning indicator, select the warning button, and choose Convert to Number. The label and availability of the warning depend on Excel’s error-checking settings, so a missing indicator is not a reason to skip checking.

Another option is Text to Columns. Select the affected column, choose Data > Text to Columns, select Delimited, choose Next twice, leave the column’s data format as General, and select Finish. For a clean column of numeric text, Excel can convert the values. Review a sample afterward, especially if the column contains codes, dates, or mixed content.

A helper column can make the conversion visible and repeatable. Suppose A2:A4 contains the text values “101”, “102”, and “103”, B2:B4 contains names, and you want to look up the numeric value 102 in E2. In C2, enter =VALUE(A2) and fill it down through C4; C2:C4 will contain real numbers. Then use =XLOOKUP(E2,C2:C4,B2:B4,"Not found"). With E2 equal to 102 and the matching name in B3, the result is the name from B3.

VALUE is appropriate for text that represents a number in a format Excel recognizes. It can return an error if the text contains unexpected characters or uses separators Excel cannot interpret under the current regional settings. Do not apply it indiscriminately to a column that mixes numeric values with labels or identifiers.

When the values should stay as text

Not every digit-only value is a quantity. Product codes, postal codes, account references, and employee IDs are often identifiers, and leading zeros may be meaningful. If a code is stored as text as “00102”, converting it to a number changes it to 102. A custom number format might display 102 with leading zeros, but that display format does not restore zeros that were never part of the stored value.

When identifiers should be text, make both sides of the lookup text consistently rather than converting the key to a number. For example, if A2:A4 contains text IDs and C2:C4 is a helper column, enter =A2&"" in C2 and fill down. If E2 contains the lookup ID, use =XLOOKUP(E2&"",C2:C4,B2:B4,"Not found"). This coerces a numeric E2 to text and compares it with the text helper values. If leading zeros have already been lost from E2, adding an empty string cannot bring them back; retrieve or rebuild the original ID first.

For a dataset with mixed types, normalize a helper column on both sides using a rule that fits the data. Avoid simply forcing everything to numbers: values such as “00102” and “102” may be different identifiers even though they have the same numeric value. Decide whether the business meaning is numeric quantity or text identity before cleaning the cells.

Other causes: spaces, errors, and wrong ranges

Text keys can also differ because of spaces or invisible characters. A value copied from an external source may have a leading or trailing space, or a nonbreaking space that ordinary cleanup does not remove. TRIM removes ordinary extra spaces from text, but cleaning should be done in a helper column and checked against representative source values. If the data contains nonbreaking spaces, a deliberate SUBSTITUTE cleanup may be needed; do not remove characters without confirming they are not meaningful.

Check for errors in the lookup cell and lookup array, too. An error value in a key can disrupt a lookup, and a formula that references the wrong column may return an unexpected result or #N/A. Confirm the lookup and return ranges cover the same records and have compatible dimensions. If a formula has been copied down, check whether its ranges shifted; use absolute references such as $A$2:$A$100 when the lookup table should stay fixed.

XLOOKUP can find numbers in unsorted data when using its default search behavior. Do not select a binary search mode merely to try to fix a failed exact match: binary search modes require the data to be sorted appropriately and can return incorrect results when that condition is not met. Likewise, approximate match modes are not a repair for a text-versus-number mismatch. Keep the default exact match unless you specifically need approximate matching and understand the data ordering.

What the match settings do—and do not do

By default, XLOOKUP searches for an exact match and searches from first to last. Its match_mode argument can request an exact match or particular approximate and wildcard behavior. Wildcards can help with text patterns when you explicitly request wildcard matching, but they do not turn a numeric value into matching numeric text. If your key is the number 102 and the cell contains text “102”, address the data type rather than changing match mode.

The optional if_not_found argument changes what appears when no match is found; it does not change how Excel compares the lookup value. For troubleshooting, a clear message can be useful, but a blank result can hide an issue. Search mode controls the direction or method of searching, not the type conversion of a key. These settings are separate from the basic requirement that the lookup value and key be comparable.

Version and compatibility checks

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including supported versions of Excel for Mac and Excel for the web. If a workbook is opened in an older Excel release that does not support XLOOKUP, the formula may display #NAME?. That is a version compatibility issue, not a number-format problem. Users of older versions may need a compatible alternative such as INDEX and MATCH or VLOOKUP, depending on the layout.

The formula logic is the same in current Excel for Windows, Mac, and the web, though ribbon locations and some interface details can differ. XLOOKUP is not available in every older Excel version, and workbook recipients may have different software. If you share a file, consider who will open it before building the workbook around a newer function.

A reliable troubleshooting sequence

Work from the data outward rather than repeatedly changing the formula. This sequence helps isolate the cause and avoids altering important keys accidentally:

  1. Confirm the XLOOKUP arguments point to the intended lookup value, lookup column, and return column.
  2. Use ISNUMBER or ISTEXT on the lookup cell and a matching-looking key cell to compare their stored types.
  3. Decide whether the values are true numbers or identifiers that should remain text.
  4. Normalize the values consistently, using conversion or a helper column appropriate to the data.
  5. Retest with the default exact match, then verify the returned value belongs to the expected row.
  6. Check for spaces, hidden characters, error values, shifted references, and software-version compatibility if the lookup still fails.

Keep the original imported column until the normalized results are verified. This is especially important when leading zeros, long identifiers, dates, or regional number formats are involved. Excel preserves only 15 significant digits of numeric precision, so very long digit sequences should generally be stored as text identifiers rather than converted to numbers.

Frequently Asked Questions

Does XLOOKUP match a number stored as text?
It may not treat a numeric value and numeric text as the same exact key. If a lookup fails despite identical-looking digits, check both cells’ types and normalize them consistently. Test with a representative row before applying a conversion to the whole dataset.

Why does XLOOKUP return #N/A for a number I can see?
The visible digits may conceal a type mismatch, extra spaces, a different key, or a range that excludes the row. Check the actual lookup cell and lookup array, then confirm that the return range is aligned with the key range. #N/A means no match was found under the formula’s current settings.

Can I use VALUE inside XLOOKUP?
Yes, VALUE can convert a lookup value that is numeric text, as in =XLOOKUP(VALUE(E2),A2:A100,B2:B100,"Not found"), when A2:A100 contains real numbers and E2 contains recognizable numeric text. If the lookup array is text instead, convert that data too or use a consistent text-based helper column.

Should I convert every number stored as text?
No. Convert values that represent quantities or numbers used in calculations. Keep identifiers such as codes and IDs as text when formatting, leading zeros, or long digit sequences matter. Changing an identifier to a number can alter its meaning or lose information.

Why does XLOOKUP show #NAME? instead of #N/A?
#NAME? can indicate that the Excel version opening the workbook does not recognize XLOOKUP, or that the function name or formula was entered incorrectly. XLOOKUP requires Excel 2021 or Microsoft 365. #N/A, by contrast, commonly means the function ran but did not find a matching key.

Related

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.