Logical & Conditional
Build an "IF NOT ERROR" Rule (Execute Custom Logic Only When Lookup Succeeds)
Invert Excel’s built-in IFERROR—which only replaces errors while passing raw successes straight through—by combining IF(ISNA(XMATCH(...)), Fallback, Custom_Success_Result) or IF(NOT(ISERROR(expr)), Success, Error).
Why IFERROR cannot return a custom message when a formula SUCCEEDS!
Spreadsheet users often ask for an "IFNOTERROR" function! Why? Built-in IFERROR(value, value_if_error) only intercepts errors—whenever value succeeds without an error, IFERROR passes the raw lookup value straight through unchanged! What if you want to return a custom badge like "Approved Vendor (Row 3)" ONLY when XMATCH finds the vendor ID, and "Unregistered Vendor" when XMATCH returns #N/A?
Reversing the branch with IF(ISNA(expr), value_if_na, custom_value_if_valid) (or IF(ISERROR(expr), value_if_error, custom_value_if_valid) for general math/text expressions) gives you exact IF-NOT-ERROR control without hiding unexpected #NAME? typos when you use ISNA on lookups!
| Goal | Why IFERROR Falls Short | Exact IF-NOT-ERROR Formula Pattern |
|---|---|---|
| Return Custom Badge on Match | IFERROR(XMATCH(...), "Missing") returns raw index 2 | =IF(ISNA(XMATCH(E2, A2:A5)), "Unregistered", "Approved (Row " & XMATCH(E2, A2:A5) & ")") |
| Apply Tax Only When Valid Number | Raw pass-through skips post-processing | =IF(ISERROR(B2 / C2), "Check Denominator", ROUND((B2 / C2) * 1.2, 2)) |
| Boolean Valid-Match Flag | Need TRUE/FALSE output | =NOT(ISNA(XMATCH(E2, A2:A5))) |
Lookup IF-NOT-ERROR Pattern: Custom Success String vs. Missing Fallback
Tests ISNA(XMATCH(D2, A2:A5)) so a successful match builds a formatted confirmation badge instead of dumping a raw row index.
=IF(ISNA(XMATCH(D2, A2:A5)), "Unregistered Vendor", "Approved Vendor (List #" & XMATCH(D2, A2:A5) & ")")General Calculation IF-NOT-ERROR Pattern Using NOT(ISERROR(...))
Executes downstream rounding and unit formatting only when B2 / B3 evaluates without #DIV/0! or #VALUE!.
=IF(NOT(ISERROR(B2 / B3)), ROUND((B2 / B3) * 100, 1) & "% Margin", "Invalid Ratio Inputs")Try the example
Use the original values or change them to test your understanding.