Excel 与 Google Sheets 查找错误完整排查指南

Tested in: Live formulas in Google Sheets, November 12, 2026. Excel-specific error names cited from Microsoft’s function documentation were not re-tested in a desktop Excel workbook on this date.

Start here: which result do you have?

Once you know the error, jump to that section. Do not wrap the formula in IFERROR until you can explain the original result. In this test, IFERROR replaced both a genuine missing-key #N/A and a VLOOKUP #REF! with the same word, “hidden.” IFNA replaced the #N/A and left the #REF! visible, which is the safer cleanup.

#N/A: the key was not found — or it only looks the same

A lookup of SKU-999 in a list that only had SKU-101 through SKU-103 returned #N/A from both XLOOKUP and MATCH:

=XLOOKUP("SKU-999", A2:A4, C2:C4)
=MATCH("SKU-999", A2:A4, 0)

That is the honest result: the key is not there. If you want a label instead of the error, use XLOOKUP’s fourth argument or wrap INDEX/MATCH or VLOOKUP in IFNA:

=XLOOKUP("SKU-999", A2:A4, C2:C4, "missing")
=IFNA(VLOOKUP("SKU-999", A2:C4, 3, FALSE), "missing")

Both returned missing.

The harder case is #N/A when you can see the value in the column. The two cells are almost never identical:

• A trailing space: SKU-103 plus one space had LEN of 8. XLOOKUP returned #N/A. TRIM of the same value returned the price 0.55. TRIM does not remove non-breaking spaces; that cleanup is covered in TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160).
• A number stored as text: a text 1003 (ISTEXT was TRUE) did not match a numeric 1003 (ISNUMBER was TRUE). In Google Sheets, this XLOOKUP returned the correct label Charlie-num only after the ID column was coerced with ARRAYFORMULA:

=XLOOKUP(F28&"", ARRAYFORMULA(A28:A29&""), B28:B29)

Writing A28:A29&"" without ARRAYFORMULA still returned #N/A. If the IDs should stay numeric and have no leading zeros, VALUE on the lookup side also worked. See Convert Numbers Stored as Text before you coerce a column that needs its leading zeros.

• The wrong column, a range that stops one row short of a new SKU, or a typo in the lookup cell.

The full cause list for XLOOKUP #N/A is in XLOOKUP Returns #N/A Even Though the Value Exists: 7 Fixes.

#REF!: the formula is pointing at a place that does not exist

=VLOOKUP("SKU-101", A2:C4, 5, FALSE) asked for the fifth column of a three-column range and returned #REF!. The column index is counted from the first column of the VLOOKUP range, not from column A of the sheet, and it has to stay inside that range.

A related failure does not always show #REF!: if you insert a column inside the table and the index is still a hardcoded 3, VLOOKUP quietly returns a different field. The formula “works.” The value is just no longer the one you expected. XLOOKUP and INDEX/MATCH avoid that, because they point at the return column itself.

Deleted rows, a renamed sheet, or a broken external reference can also produce #REF!. For IMPORTRANGE’s specific “You need to connect these sheets” #REF!, use the dedicated article: Fix “You Need to Connect These Sheets” IMPORTRANGE #REF! Error.

INDEX out of range did not return #REF! in this Sheets test. =INDEX(C2:C4, 20) returned #NUM!. Microsoft’s INDEX documentation describes #REF! when the row or column number is out of range in Excel. Believe the error in front of you, then check the row number against the height of the range.

#VALUE!: a bad argument, not a missing key

These two formulas both returned #VALUE!:

=VLOOKUP("SKU-101", A2:C4, 0, FALSE)
=VLOOKUP("SKU-101", A2:C4, -1, FALSE)

A column index has to be a positive integer. People sometimes type 0 while thinking in “zero-based” terms from programming; VLOOKUP does not work that way. Column 1 is the first column of the range.

#VALUE! also appears when a formula tries to do arithmetic on text that is not a number. "SKU-101"+C2 returned #VALUE!, and so did VALUE("SKU-101"). That is a data problem, not a lookup-function bug. Date text that will not convert is covered in Excel Dates Stored as Text: 4 Ways to Convert Them to Real Dates.

Microsoft documents #VALUE! for an XLOOKUP whose lookup array and return array have different sizes. In this Google Sheets file, =XLOOKUP("SKU-101", A2:A4, C2:C5) returned #N/A instead. Fix the heights so they match; do not rely on the error name to be the same in both apps.

#NAME?: the function is not recognized

=XLLOOKUP("SKU-101", A2:A4, C2:C4) — a one-letter typo — returned #NAME?. Sheets and Excel will not guess the intended function.

The other common source is version support. XLOOKUP does not exist in Excel 2019, 2016 or earlier. Microsoft’s XLOOKUP documentation says so. Opening a 365 file in those versions surfaces #NAME? on every XLOOKUP. The replacement is INDEX/MATCH, with 0 as MATCH’s third argument so you do not fall into approximate match. Availability and that choice are compared in XLOOKUP vs INDEX MATCH vs VLOOKUP: Which Should You Use in 2026?.

A name error can also come from a named range that was deleted, or from a locale that expects a localized function name. If autocomplete does not offer the function as you type, the name is wrong for that workbook or that version.

#SPILL! and “array result was not expanded”

XLOOKUP can return more than one cell. =XLOOKUP("SKU-103", A2:A5, B2:D5) spilled Bolt, 0.55 and West into three adjacent cells. If any of those cells already holds a value, Excel reports #SPILL! and Google Sheets reports that the array result was not expanded because it would overwrite data.

The fix is to clear the blocking cells, or to move the formula to a patch of empty sheet. Do not “fix” it by wrapping IFERROR around the formula; the collision is still there. Details for the Sheets message are in Fix “Array Result Was Not Expanded Because It Would Overwrite Data”. The Excel-table case, which is a different restriction, is in Excel #SPILL! Error Inside a Table.

The wrong value, with no error at all

This is the failure people notice last, because the cell looks successful.

The numeric list 1001, 1002, 1003, 1005 does not contain 1004. Approximate VLOOKUP returned Charlie (the name on 1003) instead of an error:

=VLOOKUP(1004, A11:B14, 2, TRUE)    → Charlie
=VLOOKUP(1004, A11:B14, 2, FALSE)   → #N/A
=MATCH(1004, A11:A14)               → 3
=XLOOKUP(1004, A11:A14, B11:B14)    → #N/A

VLOOKUP defaults to approximate match if the fourth argument is omitted. MATCH defaults to approximate match if the third argument is omitted. XLOOKUP defaults to exact match. For IDs and names, exact match is the one you want: a missing key should be #N/A, not a neighbor’s data. The longer version of this trap is in VLOOKUP Returning the Wrong Value?.

Two other silent failures:

• Duplicate keys. VLOOKUP, MATCH and default XLOOKUP return the first row. If you needed the latest entry, XLOOKUP’s search_mode -1 is the direct switch; in a related test it changed a repeated SKU-103 price from 0.55 to 0.60.
• A stale VLOOKUP column index after someone inserted a column, already mentioned under #REF!. There is no error. The returned field is just different.

A short diagnostic sequence

1. Read the error, or confirm there is no error and the value is still wrong.
2. Click the lookup cell and the apparent match in the table. Compare LEN, ISTEXT / ISNUMBER, and, if needed, UNICODE of the last character.
3. Check the match mode: VLOOKUP’s fourth argument, MATCH’s third, XLOOKUP’s match_mode.
4. Check ranges: same height for XLOOKUP’s two arrays; VLOOKUP column index inside the table; INDEX row number inside the range.
5. Confirm the function exists in this Excel version, and that the name is spelled correctly.
6. Only then decide whether IFNA should replace a genuine missing-key #N/A.

Excel vs. Google Sheets

Most of the checks above travel between the two apps. The differences worth remembering from this test and from Microsoft’s docs:

• INDEX out of range: #NUM! in this Sheets file; Microsoft documents #REF! for Excel.
• XLOOKUP with uneven array heights: #N/A in this Sheets file; Microsoft documents #VALUE! for Excel.
• XLOOKUP itself: not in Excel 2019 or earlier; present in current Google Sheets.
• Range-wide text coercion inside XLOOKUP needed ARRAYFORMULA in this Sheets file.

Argument separators follow the spreadsheet locale (comma vs semicolon).

Common mistakes

1. Treating any error as “not found.” #REF! and #NAME? are broken formulas, not missing SKUs.
2. Wrapping everything in IFERROR. It makes a typo and a missing key look the same.
3. Trusting a returned value because it is a number. Approximate match never announces itself.
4. Fixing #N/A by copying the visible text from the table into the lookup cell. That hides a space or a type mismatch that will break the next row.
5. Editing the formula when the range simply does not include the new rows.

FAQ

Why does VLOOKUP return #REF!?

The column index is larger than the width of the lookup range. =VLOOKUP("SKU-101", A2:C4, 5, FALSE) returned #REF! here because the range only has three columns.

Why do I get #N/A when I can see the value?

The two cells are not exactly equal. In this test, a trailing space (LEN 8 instead of 7) and a text ID versus a numeric ID were enough. Check spaces and data types before rewriting the formula.

Should I use IFERROR or IFNA on a lookup?

Use IFNA if you only want to replace #N/A. IFERROR also hides #REF! and #NAME?. Both behaviors were confirmed in this sheet.

Why did INDEX return #NUM! instead of #REF!?

That is what Google Sheets returned for =INDEX(C2:C4, 20). Excel’s documented error for an out-of-range INDEX is #REF!. The row number is still wrong in both cases.

Can a lookup be wrong without showing an error?

Yes. Approximate VLOOKUP for the missing ID 1004 returned Charlie, the name on 1003, with no error. Default MATCH pointed at position 3, the same row.

Related tutorials

• XLOOKUP vs INDEX MATCH vs VLOOKUP: Which Should You Use in 2026?
• XLOOKUP Returns #N/A Even Though the Value Exists: 7 Fixes
• VLOOKUP Returning the Wrong Value? The Approximate Match Trap (and 4 Other Causes)
• Convert Numbers Stored as Text in Excel and Google Sheets (5 Methods)

Live-test findings

All reproduction results above come from one Google Sheets file dated November 12, 2026. VLOOKUP column index 5 returned #REF!; indexes 0 and -1 returned #VALUE!; a typo XLLOOKUP returned #NAME?. INDEX row 20 of a 3-row range returned #NUM!. Approximate VLOOKUP for 1004 returned Charlie. IFNA left #REF! visible; IFERROR hid it.

Sources

• Microsoft Support: VLOOKUP function (#REF!, #N/A, #VALUE!, range_lookup): https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
• Microsoft Support: XLOOKUP function (availability, #N/A, #VALUE! for array size): https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
• Microsoft Support: INDEX function (#REF! when row or column is out of range): https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
• Microsoft Support: MATCH function (match_type, #N/A): https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a
• Google Docs Editors Help: VLOOKUP, XLOOKUP, INDEX, MATCH: https://support.google.com/docs/answer/3093318, https://support.google.com/docs/answer/12405947, https://support.google.com/docs/answer/3098242, https://support.google.com/docs/answer/3093378