Excel: Lookups, References and Auditable Model Design
Excel
Excel competence is judged by whether your formulas survive being copied, inserted into and audited by someone else. Master the lookup family: INDEX/MATCH is more robust than VLOOKUP because it does not break when a column is inserted and can look to the left; XLOOKUP removes the column-count argument entirely and supports a not-found value. Use SUMIFS/COUNTIFS rather than SUMPRODUCT for conditional aggregation, and wrap lookups in IFERROR only when a missing value is genuinely acceptable - masking errors hides model breakage.
Understand references cold: A1 is fully relative, $A$1 fully absolute, A$1 and $A1 mixed. Press F4 to cycle. The classic trap is copying a formula across a row where one reference should have been locked with $, producing a table that is correct in the first column and wrong in every other.
Other favourites: legacy Ctrl+Shift+Enter array formulas versus modern dynamic arrays; the difference between a what-if data table and a pivot table; and the fact that Excel stores numbers in binary floating point, so 0.1 + 0.2 does not equal 0.3 exactly and comparisons should use ROUND or a tolerance.
Formatting discipline matters: blue font for hardcoded inputs, black for formulas, green for links to other worksheets, and consistent number formats. Avoid merged cells in models - they break sorting, copying and paste-special.
Worked example: =INDEX(Price,MATCH(Product,ProductList,0)) - the zero forces an exact match. Omitting it returns an approximate match and will silently produce the wrong price.