Lookup & Reference
Lookup Product Unit Cost & Extended Gross Profit Without #N/A (XLOOKUP)
Retrieve standard unit cost from a master catalog with built-in XLOOKUP if_not_found protection and compute extended gross profit (Unit_Price - Unit_Cost) * Qty safely.
Stop #N/A and #VALUE! cascades when quoting newly added SKUs!
Sales order lines record SKU code (B2), quantity sold (C2), and unit selling price (D2), while unit COGS lives in master cost table F2:G4. Unprotected VLOOKUP throws #N/A on unmapped SKUs, and subtracting a text fallback like "Missing Cost" inside (D2 - XLOOKUP(...)) * C2 throws #VALUE! unless you verify numeric cost resolution first!
Using XLOOKUP(B2, F2:F4, G2:G4, "Missing Cost") surfaces missing catalog entries immediately without external IFNA wrappers, and checking ISNUMBER(...) before computing (D2 - Cost) * C2 yields exact extended gross profit ($480 on 15 units of SKU-20 at $84 price vs. $52 cost)!
| Order SKU (Qty = 15, Sell Price = $84) | Catalog Unit Cost | Extended Gross Profit | Error Handling |
|---|---|---|---|
| SKU-20 (Matched in Catalog) | $52 | $480 (($84 - $52) × 15) | Exact XLOOKUP default match |
| SKU-30 (Matched in Catalog) | $65 | $285 (($84 - $65) × 15) | Updates dynamically on SKU change |
| SKU-99 (Unlisted New Item) | "Missing Cost" | "Missing Cost" | Blocks #N/A and #VALUE! |
Extended Line Gross Profit With Missing-Cost Guard
Verifies XLOOKUP returns a numeric unit cost before multiplying per-unit margin (D2 - Unit_Cost) by order quantity C2.
=IF(ISNUMBER(XLOOKUP(B2, F2:F4, G2:G4, "Missing Cost")), (D2 - XLOOKUP(B2, F2:F4, G2:G4, "Missing Cost")) * C2, "Missing Cost")Error-Safe Unit Cost Lookup (XLOOKUP Built-In Fallback)
Uses XLOOKUP’s fourth argument (if_not_found) to return "Missing Cost" instead of #N/A.
=XLOOKUP(B2, F2:F4, G2:G4, "Missing Cost")Try the example
Use the original values or change them to test your understanding.