Lookup & Reference
Find the first negative projected balance with XLOOKUP
Return the first listed period with a negative projected cash or inventory balance. Sort periods chronologically before interpreting the result.
First negative period versus lowest balance
MIN(B2:B5) returns the lowest projected ending balance, -38,000 in October. XLOOKUP returns September, the first listed period with a negative ending balance.
The comparison (B2:B5<0)*1 produces {0;0;1;1}. XLOOKUP searches from the top, so dates must be in chronological order. Monthly ending balances do not establish the exact day cash runs out, and a positive month-end balance can hide an intra-month deficit.
Use numeric balances and valid, consistently ordered period labels. The fallback means no negative balance in the supplied forecast horizon; it does not establish solvency.
| Forecast Month (A) | Ending Cash (B) | Below Zero? (B < 0)*1 | Lookup Role |
|---|---|---|---|
| 2026-07 | $45,000 | 0 | Nonnegative ending balance |
| 2026-08 | $12,000 | 0 | Nonnegative ending balance |
| 2026-09 | -$14,000 | 1 (FIRST 1!) | First negative ending balance |
| 2026-10 | -$38,000 | 1 | Deeper trough (MIN, not 1st) |
First-deficit period and breach-amount formulas
Returns "No negative balance in range" automatically when every period in B2:B5 stays >= 0.
=XLOOKUP(1, (B2:B5 < 0) * 1, A2:A5, "No negative balance in range")Return the dollar value of the first negative balance
Pass B2:B5 as return_array and 0 as fallback when no deficit occurs.
=XLOOKUP(1, (B2:B5 < 0) * 1, B2:B5, 0)Try the example
Use the original values or change them to test your understanding.