IFERROR and IFNA for lookups that miss

How it works, step by step

1. 01VLOOKUP(B2,Products!$A$2:$C$500,3,FALSE) looks up the SKU in B2 and returns its Unit price from column C.
2. 02When the SKU is missing from Products, VLOOKUP returns #N/A.
3. 03IFERROR catches that error and shows "Not found" instead. When the lookup succeeds, the price passes through unchanged.
4. 04IFNA does the same job but catches only #N/A. Other errors, like #REF! after someone deletes a column in Products, still show so you can fix them. The Excel and Sheets versions below use IFNA for that reason.

The same logic in four tools

=IFNA(VLOOKUP(B2,Products!$A$2:$C$500,3,FALSE),"Not found")

=IFNA(VLOOKUP(B2,Products!$A$2:$C$500,3,FALSE),"Not found")

SELECT o.order_id,
       o.sku,
       CASE
         WHEN p.sku IS NULL THEN 'Not found'
         ELSE CAST(p.unit_price AS TEXT)
       END AS unit_price
FROM orders o
LEFT JOIN products p ON p.sku = o.sku;

import pandas as pd

result = orders.merge(
    products[["sku", "unit_price"]],
    on="sku",
    how="left",
    indicator=True,
)
result["unit_price"] = result["unit_price"].where(result["_merge"] == "both", "Not found")
result = result.drop(columns="_merge")

Mistakes that break it

• !IFERROR hides every error type, including #REF!, #VALUE!, and #DIV/0!. A broken range then shows Not found on every row and looks like missing data.
• !Returning text such as "Not found" in a price column makes row-level math on it, like =C2*D2, return #VALUE!. Return 0 when the column feeds calculations.
• !IFNA needs Excel 2013 or later, while IFERROR works back to Excel 2007. Both work in Google Sheets.
• !XLOOKUP has its own if_not_found argument, which catches only a missing match the same way IFNA does, so it needs no wrapper.

Common questions

What is the difference between IFERROR and IFNA?

IFERROR catches every error type. IFNA catches only #N/A, the error a lookup returns when it finds no match, so real formula mistakes stay visible.

How do I hide #N/A in VLOOKUP?

Wrap the lookup in IFNA: =IFNA(VLOOKUP(B2,Products!$A$2:$C$500,3,FALSE),""). The empty string shows a blank cell for SKUs that are not found.

Should I use IFERROR with VLOOKUP?

Use IFNA when you can. It handles the not-found case the same way and does not mask a #REF! or #VALUE! caused by a broken range.