XLOOKUP Not Working? Check These Format Issues

Yes—differences in the underlying data type or characters can make XLOOKUP return no match, even when two cells look identical. Cell formatting alone usually is not the cause: a number displayed as a date is still stored as a number, and changing its appearance does not convert text into a numeric value. Check the actual values, then make the lookup value and lookup range consistent.

How XLOOKUP treats formatting and values

In Excel for Microsoft 365 on Windows, XLOOKUP searches a lookup array and returns a value from a corresponding return array. It uses exact matching by default, so the lookup value must match an item in the lookup array. A different font, fill color, number format, or column width does not normally affect whether the values match. Those settings change how a value appears, not the value Excel compares.

What does matter is whether the cells contain equivalent values of the same kind. The number 123 and the text string “123” can look the same on the worksheet but behave differently in a lookup. Similarly, a date entered as a genuine Excel date is stored as a serial number, while a date imported as text is a sequence of characters. XLOOKUP may not find a match between those different underlying values.

With the lookup key in E2, keys in A2:A100, and results in B2:B100, this basic formula returns the result for an exact match or the message if no match exists:

=XLOOKUP(E2,A2:A100,B2:B100,"Not found")

The fourth argument is optional, but adding it gives a useful message instead of the default #N/A when no match is found. If the same formula works for some rows but not others, compare the specific problem values rather than changing all cell formats first.

Check whether numbers are stored as text

This is one of the most common format-related causes. Imported IDs, product codes, and amounts may be stored as text, sometimes indicated by a green triangle in the cell or left alignment under the default alignment settings. Alignment is only a clue, not proof: users can change alignment, and text may be right-aligned manually.

To check a particular cell, select it and inspect the formula bar. You can also test its type. With the suspected lookup value in E2, this formula returns TRUE if E2 contains a number and FALSE otherwise:

=ISNUMBER(E2)

If the lookup value is text but the lookup column contains numbers, convert one side consistently. When E2 contains a number written as text and A2:A100 contains numeric values, this formula converts E2 to a number before searching:

=XLOOKUP(VALUE(E2),A2:A100,B2:B100,"Not found")

That approach is appropriate only when the text represents a number and the distinction between text and number is not meaningful. Do not convert codes such as 00127 to numbers if the leading zeros are part of the identifier; converting them would turn the code into 127. Instead, keep both the lookup value and lookup column as text.

You can also convert a whole range using Excel’s available data-conversion tools, but first make a copy or confirm that changing the data type will not damage IDs. A formula-based conversion can be a safer diagnostic because it leaves the source data unchanged.

Dates can look alike but contain different values

Excel dates are stored as serial numbers and displayed according to the cell’s number format. Thus, a cell displayed as 2-Nov-2026 and a cell displayed as 11/2/2026 can be the same date value. If both are real dates, the display difference alone should not stop an exact lookup.

The problem occurs when one date is text, or when a value includes a time component. A cell can display only the date while its stored value includes a time, such as noon. A lookup for midnight on that date will not be the same numeric value as noon. To inspect this, temporarily change the cell’s number format to General and compare the displayed underlying numbers. You can also test with ISNUMBER.

If the lookup column contains real date values, use a real date value as the lookup key. With the date to find in E2, dates in A2:A100, and corresponding results in B2:B100, the formula is:

=XLOOKUP(E2,A2:A100,B2:B100,"Not found")

If the date includes times and you intend to match the calendar day regardless of time, make a helper column that contains date-only values, or use a carefully designed range-based lookup. Avoid changing the display format and assuming that this removes times; it does not alter the stored value. For text dates, convert them to real dates only after confirming the source’s day-month order, since ambiguous text can be interpreted differently.

Look for spaces and invisible characters

Text values can differ because of a leading space, a trailing space, repeated spaces, a line break, or a nonbreaking space copied from a web page. These characters may be hard to see in a cell. TRIM removes ordinary extra spaces at the beginning and end and reduces repeated ordinary spaces between words. CLEAN removes many nonprinting control characters. Neither function removes every possible Unicode character, and TRIM does not remove the nonbreaking space commonly represented by character code 160.

For a cleanup-based lookup, assume the lookup text is in E2, the names to search are in A2:A100, and the return values are in B2:B100. The formula replaces nonbreaking spaces with ordinary spaces, removes control characters, and trims the text on both sides:

=XLOOKUP(TRIM(SUBSTITUTE(CLEAN(E2),CHAR(160)," ")),TRIM(SUBSTITUTE(CLEAN(A2:A100),CHAR(160)," ")),B2:B100,"Not found")

Because Excel 365 supports dynamic array calculations, the cleanup functions can be applied to the lookup range in this formula. For a workbook that is shared with older Excel versions, a helper column may be easier to maintain and troubleshoot. Put cleaned versions of the lookup keys in that column and point XLOOKUP to it. This also makes it possible to review what the cleanup changed before relying on the results.

Use a short troubleshooting sequence

Before rebuilding a formula, work through the following checks. They help distinguish a genuine data mismatch from a range or formula problem.

Confirm that the formula’s lookup array contains the value you expect, and that the return array covers the corresponding rows.
Select the lookup value and a suspected matching item. Compare their formula-bar contents, not just their displayed appearance.
Check whether each value is text or numeric with ISNUMBER. For text, ISTEXT can help confirm the type.
Look for leading or trailing spaces, nonbreaking spaces, line breaks, and punctuation differences.
For dates, check whether both cells are real dates and whether either includes a time.
Check that the lookup and return arrays have compatible dimensions and that the formula points to the intended sheet and columns.
Use the optional not-found argument to make an ordinary missing match distinct from other formula errors.

If XLOOKUP returns #N/A, it generally has not found a match under the selected match settings. If it returns #VALUE!, review the formula’s array dimensions and arguments. A #NAME? error or a formula displayed with an _xlfn. prefix can indicate that the Excel version does not support the function. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not available as a native function in Excel 2019 and earlier.

Check the formula’s match settings and ranges

Format is not the only reason a lookup can fail. XLOOKUP’s optional match_mode argument can change what counts as a match. The default is exact match, while other settings allow wildcard matching or approximate matches. If a formula has extra arguments, verify that they are intentional. For example, wildcard mode is useful for a pattern but can produce unexpected results when a key includes an asterisk or question mark.

Also verify that the lookup array and return array start and end on corresponding rows. If keys are in A2:A100 but the return range accidentally starts at B3, a found key can return the wrong row’s result rather than fail. A table reference can reduce the risk of ranges drifting as data is added, provided the lookup and return columns refer to the intended table fields.

For a right-to-left lookup or a lookup based on a combined condition, XLOOKUP may still be suitable, but first get a simple exact-match formula working with a single key. This isolates data-format issues from more complex logic. When results are duplicated, remember that the default search direction returns the first matching item; duplicate keys are a data-design issue, not a formatting problem.

When to convert data and when to preserve it

Converting text numbers to numbers is sensible for quantities, prices, or genuine numeric measurements when calculations are expected. Preserving text is usually better for account codes, postal codes, product identifiers, and other labels where leading zeros or character patterns carry meaning. The correct choice depends on what the data represents, not how the column happens to look.

Before applying a conversion to a large imported list, check a sample that includes leading zeros, negative values, decimal separators, and any unusual entries. Regional settings can affect how text such as 1,234 is interpreted: it may represent one thousand two hundred thirty-four or a decimal value depending on the source convention. If a conversion would change meaning, clean the source or standardize both lookup sides as text instead.

For recurring imports, consider using Power Query to set column data types during the import process, or add a clearly labeled helper column that normalizes keys. Consistent source data is generally more reliable than embedding increasingly complicated conversion logic in every lookup formula.

Frequently Asked Questions

Can cell formatting alone stop XLOOKUP from finding a match?
Usually not. Changing number format, font, or fill changes display rather than the stored value. The cells may look different while matching, or look alike while containing different types or characters. Check the underlying values.

Why does XLOOKUP not match a number stored as text?
Because a text string such as “123” is not necessarily equivalent to the numeric value 123 in an exact lookup. Convert one side only when it is genuinely numeric data; preserve text for identifiers where characters such as leading zeros matter.

Will changing a cell from Text to General fix the lookup?
Not always. Changing the number format does not reliably convert existing text into a number. Use an appropriate conversion method, re-enter or transform the value, or make both sides consistently text.

Why can two dates that look identical fail to match?
One may be a text date while the other is a real Excel date, or one may include a hidden time value. Check the underlying values and confirm that both sides use the same date representation and precision.

Does XLOOKUP ignore spaces?
No. Ordinary spaces and many invisible characters are part of text comparisons. Clean the lookup value and lookup keys consistently, or use a helper column to normalize them.

What if XLOOKUP is not recognized in my Excel?
XLOOKUP requires Microsoft 365 or Excel 2021 and later. Excel 2019 and earlier do not include it as a native function, so use a compatible alternative such as INDEX with MATCH, or open the workbook in a supported version.

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.