The win
Stop pasting the same nested IF across three sheets. Define the logic once in the Name Manager as a LAMBDA, give it a name, and call it from any cell in the workbook the same way you call XLOOKUP. Excel does not care that you wrote it instead of Microsoft.
Three minutes once, every formula afterwards reads like English.
How to do it
You need Excel for Microsoft 365. The example is DAYS_LATE - a function that takes a due date and returns the number of days overdue, or zero if it isn't.
- Open the Name Manager. Formulas tab, Defined Names group, Name Manager. Or
Ctrl + F3. - Click New. In the Name field, type
DAYS_LATE. No spaces, no leading numbers. - In the Refers to field, paste this:
=LAMBDA(due_date, IF(due_date < TODAY(), TODAY() - due_date, 0) )
The argument name comes first. The expression that uses it comes last.
- Click OK. The name is now scoped to the entire workbook.
- In any cell, on any sheet:
=DAYS_LATE(A2). It autocompletes from the formula bar like a built-in. Functionally, it is one.
Why it works
LAMBDA turns an expression into a callable function. The Name Manager makes that callable function workbook-global, with the same lookup rules Excel uses for every other named function.
Two consequences worth knowing.
The reference is live, not a copy. Update the LAMBDA definition once and every formula in the workbook that calls DAYS_LATE updates with it. No find-and-replace, no hunting for stragglers on the third sheet.
The arguments are positional, exactly like SUM or VLOOKUP. The reader's mental model for "Excel function" already covers this. Anyone opening the workbook for the first time can read =DAYS_LATE(A2) without asking what it means.
That single property is the whole upgrade. Logic stops being something you maintain in eight places and starts being something you maintain in one.
When not to use it
For a formula that lives in one cell and will not be reused, the naming step is overhead with no payoff. The win is in the reuse.