IFERROR is hiding a broken regex in your spreadsheet

Somewhere in a monthly report, there is a column that has been empty since the day it was built.

Not empty because there was nothing to find. Empty because the formula never worked at all — and somebody, trying to be tidy, wrapped it in IFERROR so the red triangles would stop showing up in the summary tab.

This is not a hypothetical. It is a direct consequence of two facts about Google Sheets that almost nobody puts together, and we ran both of them in a real spreadsheet before writing this sentence.

Two different failures, one identical blank

A regular expression can fail in a Google Sheet for two completely unrelated reasons.

The pattern is invalid. Sheets runs RE2, which has no lookahead, no lookbehind, and no backreferences. A pattern like (?<=Invoice )\d+ — the natural way to grab the number after a word, and a pattern that works in every online regex tester — cannot be parsed at all. The cell returns #REF!.

The pattern is fine, and there is nothing to match. \d+ against no digits here is a perfectly good pattern asking a perfectly reasonable question with a perfectly reasonable answer: nothing. The cell returns #N/A.

Those are different errors, with different causes, requiring different responses. Now watch what IFERROR does to them:

=IFERROR(REGEXEXTRACT("Invoice 4821", "(?<=Invoice )\d+"), "")   →   (blank)
=IFERROR(REGEXEXTRACT("no digits here", "\d+"), "")              →   (blank)

The same blank. Byte for byte, cell for cell, indistinguishable.

One of those formulas has never worked, in any row, since the day it was written. The other is working exactly as intended. From the outside there is no difference, and there never will be, because IFERROR catches everything and reports nothing.

The one-word fix

IFNA is documented to catch only #N/A. We did not want to assume that “only” meant what it looked like, so we ran it:

=IFNA(REGEXEXTRACT("Invoice 4821", "(?<=Invoice )\d+"), "")   →   #REF!
=IFNA(REGEXEXTRACT("no digits here", "\d+"), "")              →   (blank)

That is the whole answer.

IFNA lets the broken pattern through, so it shows up in the cell as a loud red error you cannot miss. And it still swallows the honest no-match, which is the only thing you wanted swallowed in the first place.

Replace IFERROR with IFNA around every REGEXEXTRACT in your spreadsheet. Nothing about the working formulas changes. The broken ones stop hiding.

Proving it is not the same error wearing a different hat

It would be reasonable to suspect that #REF! and #N/A are somehow the same thing underneath, and that IFNA is catching one by accident. Sheets has two functions that can settle it, and they agree:

=ISNA(REGEXEXTRACT("Invoice 4821", "(?<=Invoice )\d+"))   →   FALSE
=ISNA(REGEXEXTRACT("no digits here", "\d+"))              →   TRUE

=ERROR.TYPE(REGEXEXTRACT("Invoice 4821", "(?<=Invoice )\d+"))   →   4
=ERROR.TYPE(REGEXEXTRACT("no digits here", "\d+"))              →   7

ERROR.TYPE returns 4 for #REF! and 7 for #N/A. ISNA says the broken pattern is not an #N/A at all. Two independent measurements, one conclusion: Sheets classifies an unparseable regular expression as a broken cell reference.

Which explains the twenty minutes you once spent looking for a deleted column that did not exist.

A formula that tells you which failure you have

If you want to audit a sheet without unwrapping every formula by hand, this returns the value when it works, and names the failure when it does not:

=IFERROR(
   REGEXEXTRACT(A1, "your pattern"),
   IF(ERROR.TYPE(REGEXEXTRACT(A1, "your pattern")) = 4, "BROKEN PATTERN", "no match")
 )

We ran all three branches:

What it was given	What it returned
(?<=Invoice )\d+ on Invoice 4821	BROKEN PATTERN
\d+ on no digits here	no match
Invoice (\d+) on Invoice 4821	4821

It is not pretty — the pattern appears twice, because ERROR.TYPE needs the error itself, not a reference to a cell that contains one. Drop it into a spare column, drag it down, and look for the word BROKEN. Then delete the column.

And then fix the pattern

IFNA and the audit formula tell you where the problem is. They do not solve it. The pattern still has to be rewritten without the construct RE2 refuses.

Almost always this means turning an assertion into a capture group. Instead of asking for “digits preceded by the word Invoice,” match the whole thing and capture the part you want:

=REGEXEXTRACT("Invoice 4821", "(?<=Invoice )\d+")   →   #REF!
=REGEXEXTRACT("Invoice 4821", "Invoice (\d+)")      →   4821

REGEXEXTRACT returns the contents of the first capture group when there is one, so the surrounding text is matched and then discarded. This is the standard translation, and it covers most of what people reach for lookbehind to do.

You can check a pattern against the rules a cell actually enforces with the tester, which was built from these runs.

Where these hide

Three places account for most of the broken patterns we have seen, and they share a shape: someone needed a value that sits next to another value, reached for lookaround, and then tidied up.

Order and invoice numbers. (?<=Order #)\d+ is the first thing anyone writes, because it is how you would say it out loud. It cannot run. Rewrite as Order #(\d+).

Email addresses out of a text column. \w+(?=@) to get the name before the @. Rewrite as (\w+)@.

Anything imported. IMPORTRANGE and CSV uploads bring columns whose contents change shape over time. A pattern that worked in January against INV-4821 returns nothing in June against INV 4821, and if it is wrapped in IFERROR the report simply gets quieter. A broken pattern and a changed data source produce the same silence, and only one of them is your fault.

What this costs you

There is a real trade-off in the IFNA recommendation, and it would be dishonest to skip it.

IFERROR was doing something for you. It caught #VALUE! when a cell contained a number instead of text. It caught #REF! when someone genuinely did delete a column. Swap in IFNA and those come back — as visible errors in a report you had made clean.

That is the point. A clean report built on a formula nobody has verified is not clean, it is quiet. If replacing IFERROR with IFNA fills your spreadsheet with red, the red was always there. You were just not being shown it.

Every claim above, executed

We do not publish a formula we have not run. Below is the actual execution log: each formula was written into a live Google Sheet through the Sheets API, and the third column is the value Google computed. Not what the documentation says, not what a model predicted.

Nine of these results were measured before they were claimed — we did not know whether IFNA would let #REF! through, so we did not write a word about it until we had asked Google. If any of this stops being true, tell us, and the correction and its date go on this page.