XLOOKUP 返回 #N/A，但值明明存在：7 種修復方法

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

• Excel for Microsoft 365 (Windows, Mac, web), Excel 2021 and Excel 2024: XLOOKUP is available, and the fixes here work as written.
• Excel 2019, 2016 and earlier: These versions don’t have XLOOKUP. Microsoft’s documentation says so directly. If you open a file that uses XLOOKUP in Excel 2019, you’ll see #NAME? instead of #N/A. Use INDEX/MATCH there. The same seven causes apply to it.
• Google Sheets: Sheets has XLOOKUP with the same argument order and the same match_mode values (0 exact, 1 and -1 approximate, 2 wildcard). Most fixes carry over. See the differences section below.

Cause 1: Extra spaces or non-breaking spaces

“SKU-101 ” with a trailing space is not “SKU-101”. Normal spaces are easy to spot with TRIM. Non-breaking spaces (character 160) come from web pages, PDFs and some exports, and TRIM does not remove them. Microsoft’s TRIM documentation says it only removes the standard space, character 32.

How to detect it

• =LEN(A2): Longer than the characters you can see?
• =UNICODE(RIGHT(A2)): 32 = normal space, 160 = non-breaking space
• =UNICODE(LEFT(A2)): Same check for a leading character

How to fix it

Clean the lookup column inside the formula:

=XLOOKUP(TRIM(SUBSTITUTE(F2,UNICHAR(160)," ")),TRIM(SUBSTITUTE(A2:A100,UNICHAR(160)," ")),B2:B100)

For a permanent fix, put =TRIM(SUBSTITUTE(A2,UNICHAR(160)," ")) in a helper column, fill it down, and use Copy > Paste Values over the original column.

Why UNICHAR and not CHAR? On Windows, CHAR(160) and UNICHAR(160) return the same character. On a Mac, CHAR and CODE use the Mac character set, so UNICHAR and UNICODE behave the same on both platforms.

Cause 2: Numbers stored as text

The number 1003 and the text “1003” look the same in a cell, but Excel’s exact match treats them as different values. This often happens with IDs from CSV files, ERP exports, or columns formatted as Text before anyone typed in them. A small green triangle in the cell corner and left alignment are common hints.

How to detect it

• =ISTEXT(A2): TRUE = the ID is text
• =ISNUMBER(F2): TRUE = your lookup value is a number
• =SUMPRODUCT(--ISTEXT(A2:A100)): How many IDs in the column are text

How to fix it

Match the lookup value’s type to the column:

Lookup is a number, column is text:

=XLOOKUP(TEXT(F2,"0"),A2:A100,B2:B100)

Lookup is text, column is numbers:

=XLOOKUP(--F2,A2:A100,B2:B100)

If the column is mixed, turn both sides into text:

=XLOOKUP(F2&"",A2:A100&"",B2:B100)

To convert the column for good, select it and choose Data > Text to Columns > Finish, or use the warning icon’s Convert to Number option.

Cause 3: The lookup range doesn’t include the value

XLOOKUP only searches the range you give it. If your data grew to row 120 but the formula still says A2:A100, any value in rows 101–120 returns #N/A. The same thing happens if you point lookup_array at the wrong column, such as searching for a name in the ID column.

Note that if lookup_array and return_array are different sizes (for example, A2:A100 and B2:B99), XLOOKUP returns #VALUE!, not #N/A. If you see #VALUE!, compare the two ranges first.

How to detect it

• =COUNTIF(A2:A100,F2): 0 = not inside the range the formula searches
• =COUNTIF(A:A,F2): 1 or more = it is in the column, just outside the range
• =MATCH(F2,A:A,0): The row number where it actually sits
• =ROWS(A2:A100)&" vs "&ROWS(B2:B100): Sizes should be equal

How to fix it

Extend the ranges, or use whole columns:

=XLOOKUP(F2,A:A,B:B)

A better long-term fix is an Excel Table (Ctrl+T). Structured references grow with your data:

=XLOOKUP(F2,Orders[ID],Orders[Customer])

Cause 4: Relative references shift when you copy the formula down

You write =XLOOKUP(E2,A2:A6,B2:B6) in the first row and fill it down. Five rows later, the formula reads =XLOOKUP(E6,A6:A10,B6:B10). The top of your table has dropped out of the range, so values near the top start returning #N/A. The giveaway is that the first rows work and lower rows fail.

How to detect it

Select a failing cell and press F2, or show the formula next to it:

=FORMULATEXT(F6)

If the range addresses changed from row to row, this is your cause.

How to fix it

Lock the ranges with $. Select the range in the formula bar and press F4:

=XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100)

Keep E2 relative so each row still looks up its own value. Table references (Cause 3) avoid the problem entirely.

Cause 5: match_mode is exact, but you need wildcards or an approximate match

By default, XLOOKUP’s match_mode is 0, exact match. In that mode, * and ? are ordinary characters, so a lookup for “Contoso*” searches for an actual asterisk. Wildcards only work with match_mode 2. This is a common trip-up for VLOOKUP users, because VLOOKUP’s exact match does accept wildcards.

The same applies to tier tables. If you look up 75 in a discount table with thresholds 0, 50 and 100, exact match finds nothing.

How to detect it

• =COUNTIF(A2:A100,F2): COUNTIF treats * as a wildcard
• =SUMPRODUCT(--(A2:A100=F2)): Literal exact matches

If COUNTIF finds a match and the exact count is 0, you need match_mode 2.

How to fix it

F2 contains a wildcard, e.g. Contoso*:

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

“contains” lookup:

=XLOOKUP("*"&F2&"*",A2:A100,B2:B100,"Not found",2)

Tier table: exact or next smaller:

=XLOOKUP(F2,A2:A100,B2:B100,,-1)

To search for a real * or ? in match_mode 2, put a tilde in front of it (~*, ~?). For tier tables, match_mode -1 returns the next smaller item and 1 returns the next larger item. Unlike VLOOKUP’s approximate match, the table doesn’t need to be sorted unless you also use binary search (search_mode 2 or -2).

Cause 6: Invisible characters and look-alike characters

Some characters survive TRIM completely:

• Line feeds (code 10) from Alt+Enter or multi-line exports
• Tabs (code 9) from copied text files
• Zero-width spaces (code 8203) from web pages and chat apps
• Full-width letters and digits from Chinese, Japanese or Korean input methods. For example, “Ｗ” (65335) is a different character from “W” (87).

Capitalization is not one of these problems. XLOOKUP isn’t case-sensitive, so “widget e” matches “WIDGET E”.

How to detect it

• =LEN(A2)-LEN(TRIM(A2)): 0 here but LEN still too long = not a normal space
• =UNICODE(LEFT(A2)): First character
• =UNICODE(RIGHT(A2)): Last character
• =UNICODE(MID(A2,3,1)): Any position in between

How to fix it

CLEAN removes codes 0–31 (line feeds and tabs included). SUBSTITUTE takes care of the zero-width space:

=XLOOKUP(F2,CLEAN(SUBSTITUTE(A2:A100,UNICHAR(8203),"")),B2:B100)

For full-width characters, ASC converts them to half-width:

=XLOOKUP(F2,ASC(A2:A100),B2:B100)

Microsoft documents ASC for double-byte (DBCS) language settings. If it doesn’t change anything in your setup, use Find & Replace or retype the value with your input method in half-width mode.

Cause 7: Imported data types: text dates, hidden times and merged cells

Data from other systems often looks right but is stored differently:

• Dates stored as text: “2026-01-07” typed or imported as text doesn’t match a real date.
• Hidden time values: A cell formatted to show only the date might hold 2026-02-03 10:30. Looking up the date 2026-02-03 (midnight) won’t match. Long decimals can differ in the same invisible way, so round both sides.
• Merged cells: Only the top-left cell of a merged area holds a value. The other cells are blank, so a multi-criteria lookup on those rows fails.

How to detect it

• =ISTEXT(A2): TRUE = text date
• =MOD(A2,1): Greater than 0 = there’s a time part
• =ISBLANK(A3): TRUE inside a merged area = no value there

How to fix it

Text dates to real dates:

=XLOOKUP(F2,DATEVALUE(A2:A100),B2:B100)

Or turn your date into matching text:

=XLOOKUP(TEXT(F2,"yyyy-mm-dd"),A2:A100,B2:B100)

Strip the time part:

=XLOOKUP(F2,INT(A2:A100),B2:B100)

Long decimals:

=XLOOKUP(ROUND(F2,5),ROUND(A2:A100,5),B2:B100)

DATEVALUE and TEXT format codes depend on your regional settings. Test both on your own data.

For merged cells, unmerge them (Home > Merge & Center to toggle off) and fill the blanks. Or add a helper column and fill it down, with the first row as =A2 and each row below it as:

=IF(A3<>"",A3,D2)

Then point XLOOKUP at the helper column:

=XLOOKUP(1,(D2:D100=F2)*(B2:B100=G2),C2:C100)

Quick diagnostic checklist

Excel vs. Google Sheets

• Syntax: Same argument order and the same match_mode/search_mode values, so plain XLOOKUP formulas copy across unchanged.
• Formulas that transform a range: Excel 365 and 2021 evaluate expressions such as TRIM(A2:A100) inside XLOOKUP automatically. In Google Sheets, wrap the formula in ARRAYFORMULA(...) if it returns an error or the wrong result.
• Characters: UNICODE, UNICHAR, CLEAN, TRIM and SUBSTITUTE exist in both.
• Excel 2019 and earlier: No XLOOKUP at all. Google Sheets doesn’t have this version problem.

Common mistakes

1. Hiding the error instead of fixing it. Adding "Not found" or IFERROR makes the #N/A go away, but the lookup still fails. Fix the data first, then add if_not_found for values that are truly missing.
2. Assuming TRIM cleans everything. TRIM only removes normal spaces. Non-breaking spaces, line feeds and zero-width spaces need SUBSTITUTE or CLEAN.
3. Changing the number format and expecting the data to convert. Formatting a text column as Number doesn’t convert values that are already in it. Use Text to Columns, Convert to Number, or --.
4. Blaming capitalization. XLOOKUP ignores case. If you actually need a case-sensitive match, use =XLOOKUP(TRUE,EXACT(A2:A100,F2),B2:B100).
5. Expecting approximate match to be fuzzy. match_mode -1 and 1 find the next smaller or larger value. They don’t find similar spellings.

FAQ

Why does XLOOKUP return #N/A when the value is clearly there?

The value you see and the value Excel compares are different. Usually there’s a hidden space, a number stored as text, or a lookup range that doesn’t include that row. Check with LEN, ISTEXT and COUNTIF to find which one.

Does XLOOKUP match numbers stored as text?

No. In Excel, the number 1003 and the text “1003” are different values for an exact match. Convert one side with TEXT(F2,“0”) or —F2, or compare both as text with F2&"" and range&"".

How do I make XLOOKUP show a blank instead of #N/A?

Use the fourth argument: =XLOOKUP(F2,A2:A100,B2:B100,""). Only do this after you’ve confirmed the value is truly missing. Otherwise you’ll hide data problems.

Is XLOOKUP case-sensitive?

No. XLOOKUP treats “ABC” and “abc” as a match. For a case-sensitive lookup, use =XLOOKUP(TRUE,EXACT(A2:A100,F2),B2:B100).

Why does XLOOKUP work in the first row but return #N/A when copied down?

The ranges are probably relative and move down as you fill the formula, so the top of your table drops out. Lock them with dollar signs, for example $A$2:$A$100, or use an Excel Table.

Related tutorials

• Fix the #SPILL! error in Excel
• VLOOKUP returning the wrong value
• TRIM not working in Excel
• Convert numbers stored as text

Download the practice file

Download xlookup-na-troubleshooting.xlsx to try every fix yourself. Each cause has its own sheet (1-Spaces through 7-Data-Types) with sample data illustrating #N/A causes, the broken formula (red), detection formulas (yellow), the fixed formula (green), and an “Expected” column so you can check your results. A Checklist sheet repeats the table above. Collation caveat: sheet 6 F10/F11 returned 85/63 rather than the labelled #N/A on the tested Mac; see Live-test findings below.

The file needs a version of Excel with XLOOKUP. Formulas that clean a whole range are saved as array formulas, so in Excel 365 they may display with curly braces {}. They also open in Google Sheets and LibreOffice Calc 24.8+. One known difference: LibreOffice matches the text “2003” to the number 2003, while Excel returns #N/A. The sheet notes this.

Live-test findings

The corrected quick-answer formula cleans both the lookup value and the lookup range. A lookup value containing U+00A0 returned 20 after both sides were cleaned in Excel and Sheets. In Sheets, use =ARRAYFORMULA(XLOOKUP(TRIM(SUBSTITUTE(F2,UNICHAR(160)," ")),TRIM(SUBSTITUTE(A2:A100,UNICHAR(160)," ")),B2:B100)). Sheets TRIM alone did not remove U+00A0.

Comparison rules matter: on Excel 16.114.4 for Mac with English UI and system region zh-Hans_US, a leading U+200B and full-width letters still matched, returning 85 and 63. EXACT was FALSE, but ordinary = was TRUE. They do not universally cause #N/A. ASC successfully converted full-width letters to half-width in that environment. In the downloadable workbook, sheet 6-Hidden-Chars, the expected #N/A labels for F10/F11 therefore do not apply to this Mac: the actual values were 85/63.

Sources

• Microsoft Support: XLOOKUP function (syntax, match_mode values, no XLOOKUP in Excel 2016/2019): https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
• Microsoft Support: How to correct a #N/A error (mismatched value types, extra spaces, Text to Columns): https://support.microsoft.com/en-us/excel/how-to-correct-a-n-a-error
• Microsoft Support: TRIM function (removes only character 32, not 160): https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9
• Microsoft Support: CLEAN function (removes codes 0–31): https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41
• Microsoft Support: CODE function (Windows ANSI vs Macintosh character set): https://support.microsoft.com/en-us/office/code-function-c32b692b-2ed0-4a04-bdd9-75640144b928
• Microsoft Support: ASC function (DBCS full-width to half-width): https://support.microsoft.com/en-us/office/asc-function-0b6abf1c-c663-4004-a964-ebc00b723266
• Microsoft Support: EXACT function (case-sensitive comparison): https://support.microsoft.com/en-us/office/exact-function-d3087698-fc15-4a15-9631-12575cf29926
• LibreOffice Help: XLOOKUP function (available since LibreOffice 24.8; used for verification): https://help.libreoffice.org/latest/en-US/text/scalc/01/func_xlookup.html
• Google Docs Editors Help: XLOOKUP function (Sheets syntax and match_mode values): https://support.google.com/docs/answer/12405947