XLOOKUP Not Working With Text? Fix the Match

Yes—XLOOKUP can look up text, and it uses an exact match by default. When it fails, the usual cause is that the lookup value and lookup column only look the same: hidden spaces, different characters, or a number stored as text can make them unequal. Check the data and match settings before changing the formula.

How XLOOKUP matches text

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel for the web. Its basic syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The lookup array contains the values to search; the return array contains the corresponding results. By default, XLOOKUP searches for an exact match and returns the first matching result it finds.

For example, with product names in A2:A6, prices in B2:B6, and the product to find in E2, use =XLOOKUP(E2,A2:A6,B2:B6,"Not found"). If E2 contains exactly the same text as an entry in A2:A6, the formula returns its price. The fourth argument is an optional message for a missing match; without it, an unsuccessful exact search returns #N/A.

Exact matching does not mean that uppercase and lowercase must agree. XLOOKUP’s normal text match is not case-sensitive, so blue and Blue match. It does mean that characters and spaces must otherwise align. A regular space at the end of a name, for instance, can make it different from the same name without that space.

Start with the error and the formula

Before editing data, confirm that the formula points to the intended lookup and return ranges. The lookup array and return array should cover corresponding rows and have the same number of cells. If they do not, XLOOKUP may return #VALUE! rather than a result.

Check the specific error too. #N/A usually means Excel did not find a match, though the reason may be invisible in the cell. #VALUE! can indicate incompatible array dimensions. #NAME? often appears when the Excel version does not recognize XLOOKUP, such as Excel 2019 or Excel 2016. In that case, use a version that supports XLOOKUP or choose an older-function alternative.

To make a missing match easier to spot, supply an informative fourth argument, such as "No matching product". This message does not fix a mismatch; it simply replaces #N/A when the search finds nothing. Avoid using an empty result as the message while diagnosing, because a blank can make a failed lookup look like a successful one.

Check spaces and hidden characters

Leading or trailing spaces are a frequent cause of text mismatches. They may be introduced when data is copied from a website, exported from another system, or entered inconsistently. The entries Northwind and Northwind look nearly identical, but the latter has a trailing space.

Excel’s TRIM function removes ordinary leading and trailing spaces and reduces repeated internal ordinary spaces to one. CLEAN removes many nonprinting characters. However, TRIM does not remove the nonbreaking space often found in copied web content. SUBSTITUTE can replace that character, represented by CHAR(160), with a normal space. For a reusable fix, make a cleaned helper column beside the source data, then look up against that column.

With the uncleaned names in A2:A6, enter this in D2 and fill down to D6: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). This standardizes many common space and control-character problems. It does not guarantee that every unusual Unicode character has been normalized, so compare suspicious values directly if the mismatch remains.

With the lookup value in E2, cleaned names in D2:D6, and results in B2:B6, use =XLOOKUP(TRIM(CLEAN(SUBSTITUTE(E2,CHAR(160)," "))),D2:D6,B2:B6,"Not found"). This cleans the lookup value in the formula and searches the cleaned helper column. Because the helper column is visible, you can also inspect what Excel is actually comparing.

Distinguish numbers stored as text

A cell can display 123 while storing the value as text, or display the same digits while storing a numeric value. Formatting and leading apostrophes can make this distinction hard to notice. XLOOKUP does not always treat a numeric 123 and the text string 123 as interchangeable. This is common with account codes, postal codes, and imported identifiers.

Decide what the data represents before converting it. If 00123 is an account code, it is usually an identifier, not a quantity; converting it to a number would discard meaningful leading zeros. In that case, standardize both the lookup value and the lookup column as text. If the entries are genuinely quantities, standardize both sides as numbers instead. Do not convert only the lookup value and expect that to correct a mixed-type lookup column.

One practical approach is to use helper columns that convert each side consistently, then point XLOOKUP to the standardized key column. For text identifiers, with source keys in A2:A6, enter =A2&"" in D2 and fill down. Make a corresponding text key from the lookup value, or ensure the input cell is stored as text, then search the helper column. Check leading zeros carefully: if Excel has already converted 00123 to the number 123, the original zeros cannot be recovered from that cell alone.

For genuinely numeric data imported as text, converting the source column to numbers may be appropriate. Use a consistent conversion for the entire key column and the lookup input, rather than relying on a visual appearance that conceals different underlying types. Keep an unchanged copy of source identifiers before applying conversions.

Use wildcard matching only when you need it

Exact matching is the right choice for full names, codes, and other keys. If you want a partial text match, set the optional match_mode argument to 2, which enables wildcard matching. The asterisk represents any number of characters, and the question mark represents one character. For example, with product descriptions in A2:A6, categories in B2:B6, and a search term in E2, use =XLOOKUP("*"&E2&"*",A2:A6,B2:B6,"Not found",2). If E2 contains mug, this finds the first description containing that text.

Wildcard searches can return an unintended result because they match partial strings and XLOOKUP returns the first match by default. If several descriptions contain the term, arrange the data or use a more specific search strategy. Wildcards are not a fix for a failed exact lookup when the real problem is a misspelled code or hidden space.

To search for a literal asterisk or question mark rather than use it as a wildcard, precede it with a tilde. For instance, a tilde escapes the wildcard character in a search pattern. This matters when product names or filenames themselves contain these symbols.

Check match and search settings

XLOOKUP has separate options for what counts as a match and which direction to search. The fifth argument, match_mode, defaults to 0 for exact match. It can also request an exact match or the next smaller or larger item, or wildcard matching. Approximate modes are mainly useful for ordered numeric thresholds; they are rarely appropriate for names or text identifiers.

The sixth argument, search_mode, controls search direction and method. By default, XLOOKUP searches from the first item to the last. A value of -1 searches from last to first, which can be useful when the latest matching record is at the bottom of a list. A reverse search still requires a match under the selected match mode; changing direction will not correct an extra space or a text-versus-number mismatch.

Also check that the ranges are aligned. If A2:A100 contains keys but B3:B101 contains results, a match can return the wrong row even though the formula calculates. Use ranges that start and end on corresponding rows, or use matching table columns. If duplicate keys exist, decide whether the first or last occurrence is the intended result.

Make text comparisons more reliable

For important workbooks, create a consistent key before building lookups. Trim accidental spaces, decide whether punctuation and capitalization matter, and store identifiers in a consistent type. If a key combines several fields, use a clearly defined combined key rather than relying on a partial name that may repeat. Document the cleanup so another person can understand why the lookup uses a helper column.

To inspect a suspected mismatch, compare the two cells’ lengths and examine their contents in the formula bar. For a quick test, copy each value into a plain-text editor or another cell and look for spaces at the start or end. Excel’s LEN function can reveal a length difference even when the displayed text looks the same. Nonbreaking spaces and visually similar Unicode characters may still require replacing or standardizing the source data.

If the result is correct for some rows and not others, check the specific failing records rather than assuming the formula is wrong. Imported columns can contain a mixture of clean text, text with hidden characters, and numeric values stored as text. A helper key makes these inconsistencies easier to identify and correct at the source.

Alternatives and version compatibility

XLOOKUP is not available in Excel 2016 or Excel 2019. In those versions, a common alternative for a left-to-right exact lookup is INDEX with MATCH. With names in A2:A6, results in B2:B6, and the lookup text in E2, use =INDEX(B2:B6,MATCH(E2,A2:A6,0)). The zero requests an exact match from MATCH. It has the same underlying need for consistent text and data types; changing functions does not automatically clean the data.

VLOOKUP can also handle text, but its lookup column must be the leftmost column in its table range, and its column number can become fragile when the table changes. XLOOKUP can return values from a column to either side of the lookup column, which is one reason it is often more convenient in current Excel. On Mac and Excel for the web, the general XLOOKUP syntax is the same; availability depends on the Excel version or subscription, not on whether the lookup value is text.

Frequently Asked Questions

Does XLOOKUP work with text?
Yes. XLOOKUP supports text and uses an exact match by default. If it returns #N/A, check for spaces, hidden characters, spelling differences, mismatched data types, and incorrect ranges.

Is XLOOKUP case-sensitive?
No. A standard XLOOKUP text match is not case-sensitive. If capitalization must distinguish otherwise identical entries, use a case-sensitive method such as combining EXACT with XMATCH and INDEX in a supported Excel version.

Why does XLOOKUP return #N/A when the text looks identical?
The cells may contain leading or trailing spaces, nonbreaking spaces, other hidden characters, or different underlying types. Compare the source values, clean ordinary spaces, and standardize numbers stored as text before searching.

Can XLOOKUP find part of a text string?
Yes. Set match_mode to 2 and use wildcard characters such as * for any number of characters. Partial matches can be ambiguous, so use this only when finding a substring is intentional.

What can I use if my Excel does not have XLOOKUP?
Excel 2016 and Excel 2019 do not include XLOOKUP. INDEX with MATCH is a common alternative for an exact lookup, but it still requires compatible data and correctly aligned ranges.

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.