Module 02 — Core Formulas

Goal

The everyday toolkit, done the robust way — lookups that don't break when a column moves, conditional aggregation, and clean date math.

Shortcuts featured

Ctrl+Enter (fill), Ctrl+D (fill down), F4 (anchor criteria ranges), name + Tab to autocomplete a function.

Formulas & concepts featured

• INDEX/MATCH over VLOOKUP: the return column can sit left of the key and won't break when columns are inserted.
• XLOOKUP: modern lookup with a built-in not-found argument.
• SUMIFS/COUNTIFS/AVERAGEIFS: sum_range goes first, then criteria pairs. Anchor the ranges with $.
• IF for a below-market flag; IFERROR to trap #N/A on a real miss (don't wrap everything blindly — it hides genuine errors).
• EOMONTH for lease-expiry / period-end month-ends.

Common mistakes

• VLOOKUP with a hardcoded column index that breaks on insert.
• Putting the sum_range last in SUMIFS.
• IFERROR swallowing a #REF! you actually needed to see.
• EDATE when you meant the month end (EOMONTH).

Your assignment

Open 02_CoreFormulas_ASSIGNMENT.xlsx: rebuild the rent-roll summary (INDEX/MATCH, XLOOKUP, SUMIFS, IF, IFERROR, EOMONTH). The Self-Check grades the summary outputs.