MO-211: Create Advanced Formulas and Macros

MO-210 tests a single-condition IF(). MO-211 expects nested and multi-condition logic: IFS() for multiple conditions without nesting IF() inside IF(), SWITCH() for matching one value against several possible results, and the *IFS family — SUMIFS(), AVERAGEIFS(), COUNTIFS(), MAXIFS(), MINIFS() — for calculations across multiple criteria at once.

LET() is the newest addition here: it lets you name a value or calculation inside a formula and reuse that name later in the same formula, which reduces repetition and errors in a long formula but has its own scoping rules — a name defined in LET() only exists within that formula.

Argument order is where people lose points across this whole group. SUMIF() takes (range, criteria, sum_range) but SUMIFS() takes (sum_range, criteria_range1, criteria1, ...) — the sum range moves from last to first between the two functions, and mixing them up produces a formula that runs without erroring but returns the wrong number.

All five lookup functions are explicitly in scope, not just the newest one. XLOOKUP() is Microsoft's newer, more flexible replacement for VLOOKUP — it can look left as well as right, has a built-in not-found argument, and doesn't require counting columns — but VLOOKUP() and HLOOKUP() are still tested directly, along with the classic INDEX()/MATCH() combination.

The most common real error with VLOOKUP() is forgetting the fourth argument (range_lookup): leaving it blank or TRUE performs an approximate match, which on unsorted data returns a plausible-looking but wrong result rather than an error — silent wrong answers are worse than visible ones on a graded task.

Know when each tool actually fits: VLOOKUP/HLOOKUP for a straightforward lookup in one direction, INDEX/MATCH when you need to look up in either direction or insert/delete columns without breaking the formula, and XLOOKUP when it's available and you want the simpler, more error-resistant syntax.

Goal Seek (Data > What-If Analysis > Goal Seek) works backward from a target result to find the input value that produces it — useful for 'what value in this cell makes that formula equal 1000' questions. Scenario Manager lets you save and switch between multiple sets of input values to compare outcomes side by side.

The Consolidate feature (Data > Consolidate) summarizes data from multiple ranges or worksheets into one, using a function like SUM or AVERAGE — different from a PivotTable in that it's a one-time calculation rather than an interactive, refreshable summary.

FILTER() and SORTBY() are dynamic-array functions: FILTER() returns only the rows matching your criteria, spilling the results across as many cells as match, and SORTBY() sorts one range by the values in another. Both are newer than Office 2019 and won't be familiar if your Excel experience predates Microsoft 365's dynamic arrays — deliberately practice them rather than assuming they behave like older functions.

PMT() calculates a loan or investment payment given rate, number of periods, and present value — the financial-function representative in this group, alongside NPER() for forecasting how many periods are needed to reach a goal.

Trace Precedents and Trace Dependents (Formulas tab) draw arrows showing which cells feed into a formula and which formulas depend on a given cell — the fastest way to understand a formula chain you didn't build yourself. The Watch Window lets you monitor a cell's value while working elsewhere in the workbook, and Evaluate Formula steps through a calculation one operation at a time to show exactly where a result goes wrong.

Macros are the one skill group on this entire domain with no equivalent anywhere on MO-210. You're expected to record a macro (Developer tab or View > Macros > Record Macro), give it a meaningful name, and make simple edits to the recorded VBA in the editor — not write VBA from scratch, but be comfortable enough to change what a recorded macro does.

A macro recorded with Relative Reference off will always act on the exact cells it was recorded against, regardless of where your selection is when you run it — a frequent source of a macro that 'runs fine' but changes the wrong cells.