IFERROR, IFNA, ISNA and ISNUMBER Equivalents in pandas

IFERROR is probably the most-used defensive function in Excel: =IFERROR(B2/C2, 0) stops a division by zero, =IFERROR(VLOOKUP(...), "not found") covers missing keys, and =IFERROR(VALUE(D2), "") swallows text that is not a number. Translating it to pandas is not a matter of finding the matching function, because pandas does not have error values at all. Each of those failures shows up differently — as inf, as NaN, or as an exception — and each needs its own handling. That turns out to be an advantage: it forces you to decide what each failure means, instead of hiding them all behind one zero. This page, part of Excel Formula Equivalents in pandas, maps each case, and the IS… functions that go with them.

Prerequisites

pip install pandas openpyxl

items.xlsx has five products with revenue and units — two with zero units — and a quantity column typed as text, with n/a, padding and a word. prices.xlsx is a price list that is missing two of the products.

import numpy as np
import pandas as pd

items = pd.read_excel("items.xlsx", dtype={"qty_text": str}, keep_default_na=False)
prices = pd.read_excel("prices.xlsx")

keep_default_na=False keeps the text n/a as text, so the conversion step below has something to fail on — by default pandas would have quietly turned it into a missing value on read.

Step 1 — Division by zero: IFERROR(B2/C2, 0)

Dividing by zero in pandas does not produce an error. A number divided by zero is inf (or -inf), and zero divided by zero is NaN:

items["price_per_unit"] = items["revenue"] / items["units"]
print(items[["sku", "revenue", "units", "price_per_unit"]])

B-2 (0 / 0) is NaN and C-3 (450 / 0) is inf. Decide what they mean, then replace them explicitly. For a price per unit, "no units sold" means the price is undefined, which is honestly represented as missing:

items["price_per_unit"] = items["price_per_unit"].replace([np.inf, -np.inf], np.nan)
print(items["price_per_unit"].tolist())

The Excel habit — IFERROR(..., 0) — would record a price of 0, which averages and charts will treat as a real, very low price. If a report genuinely needs a zero, fill it as the last step before display, not in the data. A guard that states the rule is often clearer still: np.where(items["units"] > 0, items["revenue"] / items["units"], np.nan).

Step 2 — Failed lookups: IFNA(VLOOKUP(...), "not found")

A lookup is a merge, and a key with no match gets NaN in the looked-up columns. That is the pandas form of #N/A, and filling it is the pandas form of IFNA:

looked_up = items.merge(prices, on="sku", how="left", indicator=True)
print(looked_up[["sku", "list_price", "_merge"]])

looked_up["list_price_display"] = looked_up["list_price"].astype(object).where(looked_up["_merge"] == "both", "not found")
print(looked_up[["sku", "list_price_display"]].to_string(index=False))

indicator=True adds a _merge column saying whether each row found a match, which distinguishes "no match" from "matched a row whose price is blank" — two cases IFNA cannot tell apart. Fill the display column, and keep the numeric list_price as NaN so later calculations are not fed a string. The VLOOKUP Equivalent in pandas for Excel Files covers the merge itself.

Step 3 — Failed conversions: IFERROR(VALUE(D2), "")

Converting text to numbers raises an error on the first value that does not parse. errors="coerce" turns those values into NaN instead — the pandas version of IFERROR(VALUE(...)):

try:
    pd.to_numeric(items["qty_text"])
except ValueError as exc:
    print("to_numeric:", exc)

items["qty"] = pd.to_numeric(items["qty_text"].str.strip(), errors="coerce")
failed = items["qty"].isna() & items["qty_text"].str.strip().ne("")
print(items[["sku", "qty_text", "qty"]].to_string(index=False))
print("could not convert:", items.loc[failed, "qty_text"].tolist())

Stripping first rescues " 12 "; n/a and four become NaN. The last line is the part IFERROR never does, and the reason to prefer this form: it lists what was swallowed, so four gets fixed at the source rather than silently disappearing from the totals.

Step 4 — The IS… functions as column tests

Excel's IS functions test one cell; their pandas equivalents test a whole column and return booleans you can filter or count:

tests = pd.DataFrame({
    "value": items["qty_text"],
    "ISBLANK": items["qty_text"].str.strip() == "",
    "ISNUMBER": pd.to_numeric(items["qty_text"].str.strip(), errors="coerce").notna(),
    "ISTEXT": items["qty_text"].map(lambda v: isinstance(v, str)),
})
print(tests.to_string(index=False))
print(tests[["ISNUMBER"]].sum().to_dict())

Two cautions come from how pandas stores data. A column read from Excel may hold numbers as text, so "is this a number?" is usually a question about whether the value converts — the to_numeric test — rather than about its Python type. And NaN is a float, so a strict type test such as isinstance(v, (int, float)) counts missing values as numbers; combine it with notna() when that matters.

When a function raises: a wrapper that records failures

Some transformations have no vectorised form — parsing an unusual code, calling a helper that raises on bad input. Applied with map, one bad value stops the whole column. A small wrapper is the closest pandas gets to IFERROR around a cell formula, with one improvement: it keeps a list of what failed and why:

def safe(func, errors):
    def wrapped(value):
        try:
            return func(value)
        except Exception as exc:
            errors.append((value, f"{type(exc).__name__}: {exc}"))
            return np.nan
    return wrapped

def parse_sku(code):
    prefix, number = code.split("-")
    return int(number) * (100 if prefix in "AB" else 1)

problems = []
parsed = pd.Series(["A-1", "B-2", "C3", "D-x"]).map(safe(parse_sku, problems))
print(parsed.tolist())
print(problems)

The two good codes parse, and the two bad ones become NaN with a recorded reason — a missing hyphen and a non-numeric part. Write problems to the job log or a review sheet. Catching Exception broadly is acceptable here only because every failure is recorded; a wrapper that returns NaN and forgets why is IFERROR again.

Step 5 — Do not reproduce blanket IFERROR

The most important translation is the one not to make. A workbook full of =IFERROR(..., 0) hides every kind of failure behind the same zero: missing prices, bad text, broken references. A pandas pipeline can do the same with a blanket fillna(0) at the end, and it is just as bad an idea. Keep each failure visible until you have decided what it means:

report = looked_up[["sku", "revenue", "units", "list_price"]].copy()
report["price_per_unit"] = (report["revenue"] / report["units"]).replace([np.inf, -np.inf], np.nan)
issues = {
    "no units sold": int((report["units"] == 0).sum()),
    "no list price": int(report["list_price"].isna().sum()),
    "unreadable quantity": int(failed.sum()),
}
print(issues)

Counting each kind of gap separately, and reporting it, is what IFERROR cannot do. The final display can still show 0 or "n/a" where readers expect it — Control How NaN, None and Infinity Appear in Excel Output covers how — but the decision is made per column, at the edge, with the counts in the job log.

Common pitfalls

• Expecting pandas to raise on division by zero. It returns inf or NaN; replace infinities deliberately.
• Filling lookup misses in numeric columns with text. The column becomes object and arithmetic breaks; fill a separate display column.
• errors="coerce" without a report. Values vanish into NaN just as they would behind IFERROR; always list what was coerced, and fail the job when the list contains something new.
• Default NA strings. read_excel turns n/a, NA and NULL into NaN before your code sees them; use keep_default_na=False when you need to know.

Performance notes

All of these operations are vectorised: division, replace, to_numeric with coercion and isna handle millions of values in milliseconds. The type test with map(isinstance) runs in Python per value and is the slowest; use it only on columns that genuinely mix types.

Conclusion

pandas has no error values, so IFERROR becomes three separate decisions: replace the inf and NaN that division by zero produces, fill lookup misses after a left merge (using indicator=True to tell misses from blank matches), and coerce failed conversions with to_numeric(errors="coerce") while listing what failed. The IS… functions become column tests with isna, stripped-string comparisons and to_numeric(...).notna(). Keep every failure visible and counted, and fill for display only at the end.

Frequently asked questions

What is the pandas equivalent of IFERROR? There is no single one, because pandas does not produce error values. Division by zero gives inf or NaN, a failed lookup gives NaN, and a failed conversion raises or, with errors="coerce", gives NaN. Handle each case where it arises: replace infinities, fill lookup misses, coerce conversions.

What is the difference between IFERROR and IFNA? IFNA catches only #N/A, the error a lookup returns when it finds no match. IFERROR catches every error, including #DIV/0!, #VALUE! and #REF!. IFNA is the safer choice for lookups because other errors still show.

How do I test whether a value is a number, like ISNUMBER? For a column of mixed values, pd.to_numeric(col, errors="coerce").notna() is true where the value can be read as a number. To test the Python type strictly, use col.map(lambda v: isinstance(v, (int, float))) and remember that NaN is a float.

Is a cell with spaces blank? ISBLANK says no, and so does isna(). If spaces should count as blank, test col.isna() | (col.astype("string").str.strip() == "").

Related

• Up one level: Excel Formula Equivalents in pandas — every function, translated.
• Excel IF Formulas as pandas Conditional Columns — the conditions around the error handling.
• Find #REF!, #DIV/0! and Other Formula Errors in a Workbook — errors inside workbooks you receive.
• Convert Excel Text Columns to Numbers with pandas — conversion in depth.