Lookup & Reference
Lookup price with client-specific contract override and standard rate fallback
Check a negotiated client contract rate table first and automatically fall back to your master catalog price when no custom override exists.
Negotiated enterprise overrides without duplicating your entire price book
B2B billing teams maintain a master catalog for 500 SKUs plus a short exception table holding custom negotiated prices for specific Client + SKU pairs (e.g., Acme Corp pays $78 for SKU-A instead of the $95 list price). Copying all 500 SKUs per client creates maintenance chaos whenever list prices change!
Nesting XLOOKUP's 4th [if_not_found] argument solves this cleanly in one formula: look up Client & "|" & SKU in the small Override table first; if no custom deal exists, XLOOKUP immediately runs the fallback lookup against the Master List table!
| Order Client & SKU | Override Table Match? | Master List Price | Applied Unit Price |
|---|---|---|---|
| Acme + SKU-A | Yes ($78.00 custom) | $95.00 | $78.00 (Override applied) |
| Acme + SKU-B | No (#N/A -> fallback) | $140.00 | $140.00 (List fallback) |
| Globex + SKU-B | Yes ($115.00 custom) | $140.00 | $115.00 (Override applied) |
Two-tier Override-then-Master-List XLOOKUP formula
The example stores prebuilt Client|SKU keys in E2:E3 and negotiated numeric prices in F2:F3. Master SKUs and prices are in A2:B4; order inputs are G2:H2.
Require unique master SKUs and unique override keys. Neither client nor SKU may contain the delimiter | in this representation. For unrestricted identifiers, use separate-column matching instead.
An override price of zero is a valid override. A blank price must be fixed or the override row removed: XLOOKUP’s not-found fallback is for an absent key, not a blank matched price. Validate numeric prices before invoicing.
=XLOOKUP(G2 & "|" & H2, E2:E3, F2:F3, XLOOKUP(H2, A2:A4, B2:B4, "Unknown SKU"))Audit whether an invoice line used a custom override or list rate
The source label distinguishes an override, a master-list match and a SKU absent from both. It does not validate price amounts or contractual eligibility.
=IF(ISNUMBER(XMATCH(G2&"|"&H2,E2:E3)),"Contract Override",IF(ISNUMBER(XMATCH(H2,A2:A4)),"Standard List","Unknown SKU"))Try the example
Use the original values or change them to test your understanding.