Create advanced formulas and macros
What Domain 3 tests
This domain covers function-based problem solving and automation: modern worksheet functions, lookup patterns, date arithmetic, and recorded or edited macros. Excel 2019 includes IFS, SWITCH, TEXTJOIN, MAXIFS, and MINIFS, but not XLOOKUP, FILTER, LET, or dynamic arrays, which belong to Microsoft 365.
Core rules
- IFS evaluates conditions in order and returns the first TRUE; SWITCH compares one expression to a list of values. Both avoid deep nested IFs.
- TEXTJOIN(delimiter, ignore_empty, text1, ...) concatenates ranges with a separator.
- MAXIFS/MINIFS(max_range, criteria_range1, criteria1, ...) return the largest or smallest value meeting all criteria.
- INDEX(array, row_num, [column_num]) returns a value by position; MATCH(lookup_value, lookup_array, 0) returns that position for an exact match. INDEX/MATCH can look left, unlike VLOOKUP.
- VLOOKUP with 0/FALSE is exact; with 1/TRUE it assumes ascending sort and returns the largest value less than or equal to the lookup. Omitting the fourth argument defaults to approximate.
- EDATE/EOMONTH shift by months and return the same day or month end; WORKDAY/NETWORKDAYS exclude weekends and an optional holiday range; WORKDAY.INTL supports custom weekends.
- Macros: Developer > Record Macro, stored in This Workbook, a new workbook, or PERSONAL.XLSB. Use Relative References records offsets rather than absolute cells.
Worked example
Return a department for an employee ID in A2: =IFERROR(INDEX($D$2:$D$500,MATCH($A2,$A$2:$A$500,0)),"Not found"). MATCH finds the ID position in column A; INDEX returns the same position from column D. The 0 forces exact matching, so a missing ID returns #N/A rather than a plausible wrong row, and IFERROR converts that to readable text.
Traps
- Approximate VLOOKUP on unsorted data returns incorrect matches silently.
- Hard-coded ranges break when rows are inserted; use a table or a whole-column reference where appropriate.
- Recorded macros capture Selection and ActiveCell, which fail when the active cell changes; edit them to name worksheets and ranges explicitly.
- Saving as .xlsx strips macros.