#VALUE! 錯誤：Excel 與 Google Sheets 的原因和修復

Tested in: Live formulas in Google Sheets, November 12, 2026. Microsoft’s FIND, VLOOKUP and function-error documentation is cited where Excel may differ. This is not a new desktop Excel workbook test on this date.

Which versions this applies to

The causes below appear in both current Google Sheets (tested) and in Excel. A few results are platform-specific and are flagged as such: MID with a start of 0 returned #NUM! here, not #VALUE!, and a numeric-looking text value such as "12.5" was added to 1 without an error.

Cause 1: Arithmetic on text that is not a number

="SKU-101"+10 returned #VALUE!. The SKU cannot be turned into a number, so addition stops.

Numeric-looking text is a different story. Cell E2 held the text 12.5 (ISTEXT was TRUE, LEN was 4, ISNUMBER was FALSE). In this Sheets file:

=E2+1        → 13.5
=VALUE(E2)   → 12.5
=--E2        → 12.5

Sheets coerced the text number and did the math. Do not assume #VALUE! will appear just because ISTEXT is TRUE. The error shows up when coercion fails, as it did with SKU-101.

Two related checks:

• =5+" " returned #VALUE!. A space is not a number.
• =""+5 returned 5. An empty string was treated as zero in this test.

If you need the addition to succeed, convert real numeric text first, or isolate the digits. If the value is an identifier — a SKU, a ZIP code, a value with leading zeros — leave it as text and do not add to it. Conversion methods are in Convert Numbers Stored as Text.

=TRUE+1 returned 2 and =FALSE+1 returned 1. Boolean values coerced to 1 and 0. That is easy to miss in a mixed column.

Cause 2: FIND or SEARCH when the text is not there

=FIND("z", "Apple") and =SEARCH("z", "Apple") both returned #VALUE!. Neither function uses #N/A for a failed search. =FIND("p", "Apple") returned 2, the position of the first p.

If you want a blank (or a zero) when the character is missing, IFNA will not help, because the error is not #N/A. Use IFERROR, or test first:

=IFERROR(FIND("z", A2), "not found")
=IF(ISNUMBER(FIND("z", A2)), FIND("z", A2), "not found")

SEARCH is case-insensitive and accepts wildcards; FIND is case-sensitive and does not. Both still return #VALUE! when there is no match. That behavior is also why some name-split formulas wrap FIND in IFERROR, as in Split Full Names into First, Middle and Last Name.

Cause 3: A VLOOKUP column index of 0 or less

VLOOKUP’s third argument must be a positive integer no larger than the width of the range. In a two-column table of SKUs and names:

=VLOOKUP("A", M2:O3, 2, FALSE)   → Alpha
=VLOOKUP("A", M2:O3, 0, FALSE)   → #VALUE!
=VLOOKUP("A", M2:O3, 5, FALSE)   → #REF!

Column 0 is #VALUE! (illegal argument). Column 5 of a three-column range is #REF! (the column is not there). People coming from zero-based programming languages sometimes type 0 for “the first column”; in VLOOKUP the first column is 1.

If you are getting #VALUE! from VLOOKUP, inspect the column index before you inspect the lookup value. The broader lookup-error map is in Excel & Google Sheets Lookup Errors.

Cause 4: Date text — sometimes #VALUE!, sometimes a quiet wrong date

ISO date strings subtracted cleanly in this file: ="2026-11-03"-"2026-11-01" returned 2, not an error.

DATEVALUE on the text 2026-11-03 also succeeded (serial 46329). The same function on the text 11/03/2026 also succeeded, but with a different serial, 46092. In this spreadsheet those two strings were not the same day: the slash form was read as day/month (11 March 2026), not month/day (3 November 2026). There was no #VALUE! to warn you.

So date text is only sometimes a #VALUE! problem. When the locale can parse the string, you may get a date that is simply the wrong day. Confirm with ISTEXT, then DATEVALUE, then format the result as a date and read it. The conversion methods are in Excel Dates Stored as Text.

A few results that are not #VALUE!

These are easy to mis-attribute:

• A missing XLOOKUP or VLOOKUP key is #N/A.
• A typo in a function name is #NAME?.
• =MID("Apple", 0, 2) returned #NUM! in this Sheets test, not #VALUE!. =MID("Apple", 1, 2) returned Ap.
• =INDEX(M2:M3, 20) on a two-row range returned #NUM!. =INDEX({10;20;30}, 20) on a literal array returned #REF!. Neither was #VALUE!.
• =LEFT(1003, 2)+0 returned 10. LEFT turned the number into the text 10, and +0 coerced it back.

If the cell does not actually say #VALUE!, do not apply the fixes in this article.

How to diagnose it quickly

1. Read the error. If it is #N/A or #REF!, go to the lookup-errors guide instead.
2. Click into the formula and evaluate each argument. ISTEXT and ISNUMBER show what a cell really holds.
3. For FIND/SEARCH, confirm the search text is actually in the string (and that FIND’s case matches).
4. For VLOOKUP, confirm the column index is an integer ≥ 1 and ≤ the width of the range.
5. For arithmetic, see whether the “number” contains a letter, a space, or a currency symbol the locale will not parse.

Excel vs. Google Sheets

The #VALUE! cases for SKU-101+10, FIND/SEARCH, and VLOOKUP column index 0 were clear in this Sheets test and match what Microsoft documents for those functions in Excel. Differences to keep in mind:

• Numeric text such as "12.5" added without error here. Excel often does the same for plain numeric strings, but a leading apostrophe or a locale-specific decimal mark can change that. Test the actual cell.
• MID with start 0 was #NUM! here. Do not write a fix that assumes #VALUE!.
• Date parsing follows the spreadsheet locale, which is how 11/03/2026 and 2026-11-03 became two different days in this file.

Common mistakes

1. Treating #VALUE! as “not found.” A missing key is #N/A. FIND’s #VALUE! means the substring is not there, which is close, but VLOOKUP’s #VALUE! is usually the column index.
2. Wrapping the formula in IFERROR before you know why it failed. You will also hide #NAME? and #REF!.
3. Assuming any text number will throw #VALUE!. "12.5"+1 returned 13.5 here.
4. Trusting DATEVALUE because it did not error. It can parse a slash date in the locale’s day/month order and quietly return a different day.
5. Adding a space to “clean” a number. 5+" " was #VALUE!.

FAQ

Why do I get #VALUE! from VLOOKUP?

If the third argument is 0 or negative, VLOOKUP returns #VALUE!. In this test, column 0 was #VALUE! and column 5 of a three-column range was #REF!. A missing key would have been #N/A.

Why does FIND return #VALUE! instead of #N/A?

That is how FIND and SEARCH report a failed match. =FIND("z","Apple") returned #VALUE! here. Use IFERROR or ISNUMBER(FIND(…)) if you need a fallback.

Why is adding two cells giving #VALUE!?

At least one side cannot be coerced to a number. ="SKU-101"+10 failed; a text 12.5 plus 1 succeeded. Check ISTEXT and look for letters, spaces or units in the cell.

Does IFNA fix #VALUE!?

No. IFNA only replaces #N/A. In the companion test, ="A"+1 stayed #VALUE! under IFNA and was hidden by IFERROR.

I have a date in the cell and still get #VALUE! on subtraction. What now?

The cell may still be text that this locale will not parse, or it may contain extra characters. Check ISTEXT and LEN, compare an ISO string (which subtracted cleanly here) with a slash-style date, and see the dates-stored-as-text article before forcing a conversion.

Related tutorials

• IFERROR vs IFNA: Which Should You Use for Lookups?
• Excel & Google Sheets Lookup Errors: The Complete Troubleshooting Guide
• Convert Numbers Stored as Text in Excel and Google Sheets (5 Methods)
• Excel Dates Stored as Text: 4 Ways to Convert Them to Real Dates

Live-test findings

All results above come from one Google Sheets file dated November 12, 2026. "SKU-101"+10, FIND("z","Apple"), SEARCH("z","Apple"), 5+" " and VLOOKUP(...,0,FALSE) returned #VALUE!. A text 12.5 plus 1 returned 13.5. ISO date subtraction returned 2. DATEVALUE("11/03/2026") and DATEVALUE("2026-11-03") both succeeded but produced different serials (46092 vs 46329) in this locale. MID("Apple",0,2) returned #NUM!. INDEX out of range was #NUM! on a cell range and #REF! on a literal array.

Sources

• Microsoft Support: VLOOKUP function (#VALUE! for an invalid col_index_num, #REF! when the index is too large): https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
• Microsoft Support: FIND, FINDB functions (#VALUE! when the text is not found): https://support.microsoft.com/en-us/office/find-findb-functions-c7912941-af2a-4bdf-a553-d0d155e668c3
• Microsoft Support: SEARCH, SEARCHB functions: https://support.microsoft.com/en-us/office/search-searchb-functions-9ab04538-0e55-4719-a70e-a7de8bb64902
• Microsoft Support: DATEVALUE function: https://support.microsoft.com/en-us/office/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252
• Google Docs Editors Help: FIND, SEARCH, VLOOKUP, DATEVALUE, VALUE: https://support.google.com/docs/answer/3094125, https://support.google.com/docs/answer/3094154, https://support.google.com/docs/answer/3093318, https://support.google.com/docs/answer/3093039, https://support.google.com/docs/answer/3093659