DEV Community: SheetError

ISBLANK is FALSE on a cell that looks empty (and IFBLANK doesn't exist) in Google Sheets

SheetError — Sun, 01 Nov 2026 06:06:14 +0000

ISBLANK(B3) returns FALSE, but B3 looks completely empty. And =IFBLANK(B3,"none") throws #NAME?. Both are expected behavior. Here's what each "empty" test actually checks.

Three cells that all look empty

	B	`=ISBLANK(B)`	`=B=""`	`=LEN(B)`
2	(never touched)	TRUE	TRUE	0
3	`=""`	FALSE	TRUE	0
4	a single space	FALSE	FALSE	1

ISBLANK asks "is this cell truly empty?" ="" returns an empty text string, which counts as content, so ISBLANK is FALSE.
="" asks "does this look like empty text?" TRUE for untouched cells and "", FALSE for 0 and for a space.
A space fails both tests. LEN gives it away. TRIM(B4)="" is TRUE. (A non-breaking space, CHAR(160), needs SUBSTITUTE.)

IFBLANK is not a Sheets function

IFBLANK sounds like it should exist next to IFNA and IFERROR, but neither Sheets nor Excel has it, so #NAME? is correct. Use one of these:

=IF(B3="","none",B3)          → "display empty": untouched cells AND formula blanks
=IF(ISBLANK(B2),"type hours",B2)   → "the user hasn't typed anything yet"

They are not interchangeable. Pick the one that matches what "empty" means for that column.

Return a true blank from your own formulas

If downstream formulas use ISBLANK, don't output "" or " ". Leave the argument empty instead:

=IF(A2="",,A2*10)

The omitted argument between the two commas returns a real blank (not ""), so ISBLANK on that cell is TRUE.

Counting blanks disagrees too

COUNTA counts "" formulas as filled.
COUNTBLANK counts "" as blank.

If your dashboard mixes COUNTA, COUNTBLANK and ISBLANK, that's why the numbers don't match.

Quick diagnosis

ISBLANK is FALSE on an empty-looking cell? Click it and look at the formula bar:

="" or a formula → use ="" tests, or return a true blank.
nothing visible but LEN > 0 → a space or CHAR(160); clean it.
#NAME? with IFBLANK → replace with IF(A2="",…).

Originally published on SheetError, a free library of Google Sheets error fixes (I run it).

This article was created with the help of AI.

VLOOKUP returns #N/A but the value is right there: numbers stored as text and trailing spaces

SheetError — Wed, 28 Oct 2026 22:05:58 +0000

You can see the key in the table, and VLOOKUP(..., FALSE) still says #N/A. That's almost never a missing row. Exact match compares values, not what the cell looks like. The two usual culprits are a number vs text mismatch, and a trailing space.

Recreate both

	A (sku)	B (name)	D (lookup)	E
2	101 (number)	Widget	`'101` (text)	`=VLOOKUP(D2,A2:B2,2,FALSE)` → #N/A
3	`apple` (trailing space)	Fruit	apple	`=VLOOKUP(D3,A3:B3,2,FALSE)` → #N/A

Check the keys instead of guessing

=ISNUMBER(A2)   → TRUE
=ISTEXT(D2)     → TRUE     same digits, different types
=LEN(A3)        → 6
=LEN(D3)        → 5        same word, different lengths

Alignment is a hint (text left, numbers right), but ISNUMBER/ISTEXT and LEN are the proof.

A quick confirmation trick: retype D2 as a plain number (no apostrophe). E2 becomes Widget. Put the apostrophe back and #N/A returns. That toggle tells you it's a type problem, not a missing row.

The trap: TRIM on the wrong side

=VLOOKUP(TRIM(D3),A3:B3,2,FALSE)   → still #N/A

The extra space lives in the table, not in the lookup value. Trimming D3 can't remove it.

Fixes

Number vs text – convert the side that's wrong:

=VLOOKUP(VALUE(D2),A2:B2,2,FALSE)   → Widget

If instead the table holds text and your lookup is a number, convert the lookup with TO_TEXT(D2) (or clean the table column). Don't wrap both sides and hope.

Space in the table – trim the table side:

=VLOOKUP(D3,ARRAYFORMULA({TRIM(A3:A),B3:B}),2,FALSE)

For real data, a cleaned helper column is easier to maintain. (In locales that use ; as the argument separator, the comma between columns inside { } becomes a backslash: {TRIM(A3:A)\B3:B}.)

Still missing after TRIM? Check LEN again: Google's TRIM doesn't remove a non-breaking space, CHAR(160) (common in data pasted from the web). Turn it into a normal space first: TRIM(SUBSTITUTE(A3,CHAR(160)," ")).

Don't reach for IFNA yet

IFNA(VLOOKUP(...),"") is fine for keys that really aren't there. When the row should match, hiding the #N/A just hides the type bug. Fix the keys first, then add IFNA for true misses.

Originally published on SheetError, a free library of Google Sheets error fixes (I run it).

This article was created with the help of AI.

Fix "Function ADD parameter 2 expects number values" (#VALUE!) in Google Sheets

SheetError — Tue, 27 Oct 2026 22:13:03 +0000

You type =A2+B2, and instead of a total the cell shows #VALUE!. Hover it and the tooltip says:

Function ADD parameter 2 expects number values. But 'pcs' is a text and cannot be coerced to a number.

The formula parsed and the function name is fine. Sheets wanted a number and got text it can't turn into a number. Here's how to reproduce it, how to tell it apart from look-alike errors, and three fixes that don't involve IFERROR.

Reproduce it in 30 seconds

	A (qty)	B (unit)	C
2	10	pcs	`=A2+B2` → #VALUE!
3	10	2	`=A3+B3` → 12

Same operator, different input type. Two more ways to hit the same error:

=VALUE("pcs") → #VALUE! (VALUE only converts text that already looks like a number, date or time)
=DATEVALUE("Widget") → #VALUE!

And the case that surprises people: type '10 (apostrophe, so it's text) in G2, then enter =G2+1 in G3. You get 11. Arithmetic will coerce a text number like '10. It will not coerce pcs. That is the whole difference between C2 failing and G3 working.

Why SUM doesn't complain but + does

=SUM(A2:B2) returns 10, not an error. SUM skips text silently. The + operator doesn't. So if a total is "too low" with SUM and errors with +, you have the same root cause: a text cell in a numeric column.

It is not one of these

#N/A is a lookup/filter miss. Nothing is being looked up here.
#NAME? is an unknown function name (VLOKUP). Nothing is misspelled here; the + operator (ADD) rejected its argument.
#ERROR! / Formula parse error means the formula couldn't be read at all (a missing bracket or quote, or commas vs semicolons for your locale). This formula parsed.
#NUM! is a number outside a function's domain (e.g. SQRT(-1)). Here the type was wrong before the domain mattered.

Fixes

1. Keep units out of the math. The quantity column holds numbers only. Put the unit in the header, or add it after the calculation:

=A2&" pcs"      → "10 pcs" (text, for display only)

2. Convert only real text numbers.

=VALUE(G2)                                   → 10 when G2 is '10
=VALUE(REGEXEXTRACT(SUBSTITUTE("1,200.5 pcs",",",""),"[0-9]+(?:\.[0-9]+)?"))   → 1200.5, keeps decimals and drops thousands commas

The regex takes only the first number, and signs or leading-dot decimals are read wrong without an error (-3.5 becomes 3.5, .5 becomes 5). On comma-decimal locales, swap the separators; see https://sheeterror.com/tutorials/formula-parse-locale/ .

Don't wrap every #VALUE! in VALUE and hope; VALUE("pcs") still fails.

3. Guard the type when a column may legitimately hold text (n/a, pending…):

=IF(ISNUMBER(B2), A2+B2, "")

Why not IFERROR?

IFERROR(A2+B2, 0) makes the cell look fine, but it also hides #REF! and #DIV/0! if the formula breaks differently later, and it turns "someone typed pcs in the qty column" into a silent wrong total. Fix the input or test the type with ISNUMBER.

Originally published on SheetError, a free library of Google Sheets error fixes (I run it). There's also a 76-second video walkthrough on the SheetError YouTube channel.

This article was created with the help of AI.