Information
ISNA function: test specifically for #N/A missing-lookup results
Return TRUE only when a lookup produces #N/A—while returning FALSE on #DIV/0! or valid matches—to reconcile unmatched IDs cleanly.
Why reconciliation audits use ISNA instead of ISERROR
When reconciling a shipment manifest against a master SKU list, an unmatched SKU produces #N/A ("not found in master"), whereas a corrupted formula produces #DIV/0! or #VALUE!. ISNA(value) returns TRUE exclusively for #N/A and FALSE for every other value or error.
Pairing ISNA(XMATCH(sku, master_list)) creates a clean TRUE/FALSE column marking new SKUs that need onboarding.
| Expression | Evaluated Value | ISNA Result | ISERROR Result |
|---|---|---|---|
| XMATCH("SKU-10", A2:A4) | 1 (found) | FALSE | FALSE |
| XMATCH("SKU-99", A2:A4) | #N/A (missing) | TRUE | TRUE |
| 100 / 0 | #DIV/0! (math bug) | FALSE | TRUE |
Flag missing keys without hiding broken math
Because ISNA(100/0) evaluates to FALSE, a broken division will never be misclassified as an "Unlisted SKU" during data validation.
=IF(ISNA(XMATCH(D2, A2:A4)), "Unlisted SKU", "Matched")Verify onboarding once the missing SKU is added
As soon as the missing code is appended or corrected in the lookup range, XMATCH returns its numeric position and ISNA flips from TRUE to FALSE.
=ISNA(XMATCH(D2, A2:A4))Try the example
Use the original values or change them to test your understanding.