Perform Operations by Using Formulas and Functions
Reference control is the foundation
Most formula errors in exams come from references, not functions. Master relative (A1), absolute ($A$1) and mixed ($A1, A$1) references, 3D references such as =SUM(Jan:Mar!B2), defined names, and the fill behaviour each produces. Then know the core families: logical (IF, AND, OR, IFS, SWITCH), lookup (VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP), text (LEFT, MID, RIGHT, TEXTJOIN, TEXT), date (TODAY, EOMONTH, EDATE, NETWORKDAYS, WORKDAY), conditional aggregation (SUMIFS, COUNTIFS, AVERAGEIFS), and error handling (IFERROR, IFNA). Dynamic array functions (FILTER, SORT, UNIQUE, SEQUENCE) spill into neighbouring cells and use the # spilled-range operator.
Traps exam writers like
- VLOOKUP's fourth argument defaults to TRUE (approximate). Almost every exam question needs
FALSEor0for an exact match, and the lookup column must be the first column of the range. - In INDEX/MATCH, INDEX takes the row then column order:
=INDEX(Return,MATCH(key,Lookup,0)). - SUMIFS puts sum_range first; SUMIF puts range first. Criteria containing an operator must be quoted:
">=100". - A date stored as text will not match a real date criterion.
#SPILL!means the spill area is blocked;#N/Afrom XLOOKUP can be replaced with its fourth argument or IFNA.
Worked example
=SUMIFS(Sales[Amount],Sales[Region],"West",Sales[Date],">="&DATE(2024,1,1)) - sum range first, each criterion paired with its range, and the date operator concatenated to the DATE function.