Excel Formulas skill for Codex

A good formula returns the right answer for the edge cases (missing key, blank, text number, duplicate), survives inserted rows and columns, is readable by the next analyst, and fails loudly where a silent wrong number would be worse than an error.

Workflow

1. Ask (or infer from the file) which Excel the audience runs: Microsoft 365 / Excel 2021+ has XLOOKUP, dynamic arrays and LET; Excel 2016/2019 does not. LAMBDA and its helpers need 365 or 2024. Google Sheets supports XLOOKUP, FILTER, LET and LAMBDA.
2. Look at the data shape first: header row, key uniqueness, data types (numbers stored as text), whether the source is an Excel Table (prefer structured references if so).
3. Write the formula, then test it against: a normal key, a missing key, a blank, a duplicate key, and the first and last rows.
4. Explain it in one or two sentences, naming each argument’s role.

Pick the function

Need	Use	Avoid
Exact lookup by key	XLOOKUP(key, keys, results, "Not found")	VLOOKUP(..., TRUE) default approximate match
Lookup on 365-less Excel	INDEX(results, MATCH(key, keys, 0))	VLOOKUP with a hard-coded column number
Two-way (row and column)	INDEX(grid, XMATCH(r, rows), XMATCH(c, cols))	Nested IFs
Bracket / tier lookup	XLOOKUP(x, lower_bounds, rates, , -1) (next smaller)	Long nested IF chains
Conditional sum / count / average	SUMIFS, COUNTIFS, AVERAGEIFS	SUMPRODUCT unless criteria need math
Return all matching rows	FILTER(range, condition, "None")	Helper columns with SMALL(IF(...))
Distinct list, sorted	SORT(UNIQUE(range))	Remove Duplicates (not live)
Reuse an intermediate value	LET(name, expr, ...)	Repeating the same sub-expression
Reusable custom function	Named LAMBDA in Name Manager	VBA UDF (blocked in many orgs)
Text splitting / joining	TEXTSPLIT, TEXTJOIN, TEXTBEFORE, TEXTAFTER	MID(FIND(...)) stacks

Patterns

// Lookup with a fallback and exact match (default match_mode 0)
=XLOOKUP([@CustomerID], Customers[ID], Customers[Region], "Unknown")

// Multiple criteria: boolean arrays multiply to 1 only where all are true
=XLOOKUP(1, (Orders[Region]=G2) * (Orders[Month]=H2), Orders[Total], 0)

// Last match instead of first (search_mode -1)
=XLOOKUP(G2, Log[User], Log[Timestamp], , 0, -1)

// Commission tier: thresholds sorted ascending, take the next smaller match
=XLOOKUP(B2, Tiers[From], Tiers[Rate], 0, -1)

// Top 5 orders for a region, sorted by total descending, spilled
=TAKE(SORT(FILTER(Orders, Orders[Region]=G2, "No orders"), 4, -1), 5)

// Readable multi-step calculation
=LET(
   net,   SUMIFS(Sales[Net], Sales[Month], G2),
   cost,  SUMIFS(Costs[Amount], Costs[Month], G2),
   IF(net = 0, "", (net - cost) / net)
)

// Named function: Name Manager > New > Name: GROSSUP
=LAMBDA(net, rate, ROUND(net * (1 + rate), 2))
// then in cells: =GROSSUP(B2, Inputs!$B$1)

// Row-wise logic over a spill without helper columns
=BYROW(B2:D20, LAMBDA(r, MAX(r) - MIN(r)))

Spill rules: refer to a whole spilled result with # (=SUM(F2#)); a spill needs empty cells below/right, otherwise #SPILL!; spills cannot go inside an Excel Table.

Debugging

Error	Usual cause	First check
#N/A	Key not found: trailing spaces, number vs text, wrong range	=ISNUMBER(key) vs =ISNUMBER(first key); =LEN(key)
#VALUE!	Text in arithmetic, wrong argument type, mismatched array sizes	Evaluate Formula; check ISTEXT on inputs
#REF!	Deleted row/column or sheet the formula pointed at	Trace Precedents; restore from version history
#NAME?	Misspelled function, missing quotes, function newer than this Excel, or _xlfn missing when written by code	Re-type the function name; check Excel version
#SPILL!	Something occupies the spill range, or spilling inside a Table	Click the error badge; clear the range
#DIV/0!	Empty or zero denominator	Decide: blank (IF(d=0,"",n/d)) or keep the error
#CALC!	FILTER with no matches and no if_empty, or empty array	Add the third argument
Wrong number, no error	Numbers stored as text ignored by SUMIFS, duplicate keys, absolute/relative refs shifted on copy	=SUMPRODUCT(--ISTEXT(range)); COUNTIF(keys, key)>1

Technique, in order:

1. Formulas > Evaluate Formula, step through until the first unexpected intermediate.
2. Select part of the formula in the bar and press F9 to see that piece’s value (then Esc, never Enter, or the result replaces the expression).
3. Check calculation mode (Formulas > Calculation Options > Automatic); manual mode makes correct formulas show stale values.
4. Clean inputs at the source rather than wrapping lookups: TRIM, CLEAN, SUBSTITUTE(x, CHAR(160), "") for non-breaking spaces from web exports, VALUE or Data > Text to Columns for text numbers.

Use IFERROR only around the specific expected failure and return something deliberate. A blanket IFERROR(..., 0) turns a broken reference into a plausible zero. Prefer IFNA for lookups so other errors still surface.

Performance

• Volatile functions (OFFSET, INDIRECT, TODAY, NOW, RAND, CELL, INFO) recalculate on every change. Replace OFFSET dynamic ranges with Tables or INDEX(A:A,1):INDEX(A:A,n).
• Whole-column references (A:A) are fine for SUMIFS/XLOOKUP but slow in array math ((A:A=x)*(B:B=y)); use Table columns or bounded ranges.
• Compute a repeated lookup once in LET or a helper column instead of in every cell.
• Thousands of identical SUMIFS over a large table: consider a PivotTable or GROUPBY/PIVOTBY (Microsoft 365) instead.

Writing formulas from code

Files store formulas in an older, English, comma-separated syntax. Functions added after Excel 2010 must carry a prefix or Excel shows #NAME?:

Function	Stored as
XLOOKUP, XMATCH, UNIQUE, SEQUENCE, IFS, MAXIFS, MINIFS, TEXTJOIN, CONCAT, SWITCH	_xlfn.NAME(
FILTER, SORT, SORTBY	_xlfn._xlws.NAME(
LET, LAMBDA	_xlfn.LET( / _xlfn.LAMBDA(, with each parameter as _xlpm.name

XlsxWriter adds the prefixes and marks dynamic arrays for you:

import xlsxwriter

wb = xlsxwriter.Workbook("report.xlsx", {"use_future_functions": True})
ws = wb.add_worksheet("Report")
ws.write_dynamic_array_formula("E2", '=SORT(UNIQUE(A2:A500))')
ws.write_formula("F2", '=XLOOKUP(E2, A2:A500, C2:C500, "n/a")')
ws.write_formula("G2", "=LET(_xlpm.t, SUM(C2:C500), _xlpm.t/COUNT(C2:C500))")
wb.close()

openpyxl writes the formula string verbatim: add prefixes yourself (ws["F2"] = '=_xlfn.XLOOKUP(E2,A2:A500,C2:C500,"n/a")'). openpyxl cannot mark a formula as a dynamic array, so spilling functions written with it open as =@FILTER(...) and return one value. Use XlsxWriter for new files with spills, or have the formula return a single value.

Also from code: use commas as argument separators and . decimals even when the user’s Excel is set to a locale with semicolons; quote sheet names with spaces ('Q3 Data'!A1); make column references absolute where the formula will be copied ($B$1).

For the workbook mechanics (formatting, tables, saving, recalculation) see $xlsx-spreadsheets.

Verification

• Build a small test block next to the formula (or in a scratch sheet) with the edge cases listed in the workflow and their expected results; confirm each.
• For generated files, open in Excel or recalculate and scan for errors with the audit in $xlsx-spreadsheets; LibreOffice 24.8+ evaluates XLOOKUP, FILTER, SORT, UNIQUE and LET, older versions show errors for them, so do not trust an old converter’s results.
• Cross-check one aggregate with an independent method (pivot, Python, calculator).

Answer format

**Formula (F2):** `=XLOOKUP(1, (Orders[Region]=G2)*(Orders[Month]=H2), Orders[Total], 0)`
**How it works:** builds a 1/0 array that is 1 only where region and month both match,
then returns the Total from the first such row; 0 if none match.
**Needs:** Excel 365 or 2021+. For Excel 2019 use
`=IFERROR(INDEX(Orders[Total], MATCH(1, (Orders[Region]=G2)*(Orders[Month]=H2), 0)), 0)`.
**Tested:** match, no match (0), duplicate rows (first wins - confirm that is intended).