Lookup & Reference
XLOOKUP and VLOOKUP with a validated, zero-safe manual price override
Use a nonnegative numeric override, including zero, and fall back to catalog pricing only when the override is blank.
Let reps override catalog pricing on specific lines without breaking $0.00 promos!
A blank override means use the catalog price. A numeric zero means an intentional free item and must remain zero. In this price example negative numbers, spaces, text, logical values and error values are invalid overrides; they must not silently fall back to a different price. A formula returning an empty string counts as blank.
Enter a nonempty text SKU. A valid override takes precedence even when that SKU is not in the catalog; it does not verify product eligibility. When using the catalog, this small example requires all three prices to be numeric and nonnegative. Missing prices must not become free items.
The sample uses plain SKU identifiers. Both lookups return the first match and ignore case. Keep catalog SKUs unique and avoid wildcard characters when comparing the legacy VLOOKUP variant with XLOOKUP. This example does not enforce authorization to override prices.
| SKU (Col A) | Manual Override (Col B) | Catalog Price | Final Billed Price (ISNUMBER(B2)) |
|---|---|---|---|
| SKU-102 | (blank) | $80 | $80 (pulls standard catalog rate) |
| SKU-102 | 65 | $80 | $65 (uses negotiated override) |
| SKU-102 | 0 | $80 | $0 (preserves explicit $0 promo!) |
| SKU-999 | (blank) | Not in catalog | "Unlisted SKU" |
Zero-Safe Manual Override + XLOOKUP Catalog Fallback
Validate the SKU and override first. Only a blank override reaches the catalog lookup. Numeric zero remains an explicit override.
=IF(OR(NOT(ISTEXT(A2)),COUNTBLANK(A2)>0),"Enter SKU",IF(ISNUMBER(B2),IF(B2<0,"Invalid override",B2),IF(COUNTBLANK(B2)=0,"Invalid override",IF(COUNT($E$2:$E$4)<>3,"Invalid catalog",IF(MIN($E$2:$E$4)<0,"Invalid catalog",XLOOKUP(A2,$D$2:$D$4,$E$2:$E$4,"Unlisted SKU"))))))Legacy VLOOKUP + IFNA Equivalent
Use exact-match VLOOKUP with the same input policy. IFNA supplies the missing-SKU message after catalog validation. This example assumes plain, unique SKU codes.
=IF(OR(NOT(ISTEXT(A2)),COUNTBLANK(A2)>0),"Enter SKU",IF(ISNUMBER(B2),IF(B2<0,"Invalid override",B2),IF(COUNTBLANK(B2)=0,"Invalid override",IF(COUNT($E$2:$E$4)<>3,"Invalid catalog",IF(MIN($E$2:$E$4)<0,"Invalid catalog",IFNA(VLOOKUP(A2,$D$2:$E$4,2,FALSE),"Unlisted SKU"))))))Try the example
Use the original values or change them to test your understanding.