Lookup & Reference
XLOOKUP with IF: Negotiated Rate Override vs Standard Price Fallback
Combine XLOOKUP, LET, and LEN-based IF logic so negotiated client overrides supersede standard catalog rates while preserving intentional 0-fee waivers and flagging unknown accounts.
Why testing Override > 0 breaks zero-fee waivers and warranty replacements
In B2B billing tables, column B stores each account’s Standard_Rate while column C stores an optional Negotiated_Override (left blank when the standard rate applies). Look closely at four accounts in A2:C5: CL-01 (150, blank), CL-02 (150, 125), CL-03 (200, 0 waived warranty rate), and CL-04 (180, blank).
If a learner writes =IF(XLOOKUP(F2, A2:A5, C2:C5) > 0, XLOOKUP(F2, A2:A5, C2:C5), XLOOKUP(F2, A2:A5, B2:B5)), two problems occur: first, XLOOKUP scans the table up to three times per row; second, CL-03’s intentional 0 override fails > 0 and wrongly bills 200! Binding both lookups once inside LET and testing LEN(ovr_rt) > 0 preserves 0 overrides while falling back cleanly on true blanks.
| Account | Standard_Rate | Negotiated_Override | Effective Billed Rate |
|---|---|---|---|
| CL-01 | 150 | (blank) | 150 (Standard fallback) |
| CL-02 | 150 | 125 | 125 (Negotiated override) |
| CL-03 | 200 | 0 | 0 (Waiver preserved via LEN > 0) |
| CL-99 | — | — | "UNKNOWN_CLIENT" |
Single-Pass LET + XLOOKUP + IF Override Formula
Look up std_rt (with "UNKNOWN_CLIENT" missing-key default) and ovr_rt inside LET, then test LEN(ovr_rt) > 0 so numeric 0 (length 1) overrides std_rt while empty cells (length 0) fall back to std_rt.
=LET(std_rt, XLOOKUP(F2, $A$2:$A$5, $B$2:$B$5, "UNKNOWN_CLIENT"), ovr_rt, XLOOKUP(F2, $A$2:$A$5, $C$2:$C$5, ""), IF(std_rt = "UNKNOWN_CLIENT", "UNKNOWN_CLIENT", IF(LEN(ovr_rt) > 0, ovr_rt, std_rt)))Extended Invoice Amount Check
Multiply the resolved rate by billable hours F3 when numeric, and test granting CL-01 a new 135 override to confirm live recalculation.
=IF(ISNUMBER(G2), ROUND(G2 * F3, 2), G2)Try the example
Use the original values or change them to test your understanding.