Lookup & Reference
Get the first non-blank value in a row or column (COALESCE pattern)
Implement SQL-style COALESCE fallback across price or contact columns with XLOOKUP—while keeping valid numeric zeros intact.
Price-override waterfall without skipping valid $0 entries
Pricing sheets often check OverridePrice first, then PromoPrice, then ListPrice—just like SQL's COALESCE(a, b, c).
Testing (ISNUMBER(A2:C2) + (ISTEXT(A2:C2) * (A2:C2<>""))) > 0 inside XLOOKUP(TRUE, ..., A2:C2, "All blank") matches the first populated number (including a $0 free-tier promo!) or non-empty text string while skipping empty cells and "" formula blanks.
This pattern selects numbers and nonempty text, not all data types: boolean values are excluded, and input errors can propagate. For prices, separately validate that the selected value is an allowed numeric amount.
| Row | OverridePrice (A) | PromoPrice (B) | ListPrice (C) | Effective Price |
|---|---|---|---|---|
| 2 | (blank) | 0 | 49 | 0 (free promo preserved!) |
| 3 | (blank) | (blank) | 79 | 79 (falls back to List) |
Zero-preserving COALESCE formula
On row 2, OverridePrice A2 is empty and PromoPrice B2 is 0, so the waterfall returns 0 rather than skipping ahead to $49.
=XLOOKUP(TRUE, (ISNUMBER(A2:C2) + (ISTEXT(A2:C2) * (A2:C2<>""))) > 0, A2:C2, "All blank")Fallback to ListPrice and All-Blank guard
On row 3 both A3 and B3 are empty, so XLOOKUP falls through to ListPrice C3 (79).
=XLOOKUP(TRUE, (ISNUMBER(A3:C3) + (ISTEXT(A3:C3) * (A3:C3<>""))) > 0, A3:C3, "All blank")Try the example
Use the original values or change them to test your understanding.