IntermediateLookup & Reference
Look up a customer's region with XLOOKUP
Match a customer ID against a reference table to return their region.
XLOOKUP
8 min25 points
Excel for business analysts
A business analyst turns a vague question into a number someone will act on, and has to be able to defend how they got there.
What the job actually asks for
Translating a question into a measurable metric, comparing segments, and building reporting that someone else can rerun next month. The spreadsheet is as much a document of your reasoning as a calculation.
The recurring tasks
- Translate a question into a measurable metric
- Compare performance across segments
- Build repeatable reporting templates
- Document assumptions behind a model
Where people get caught
Building something that only works once. A one-off analysis with values pasted in cannot be rerun, so next month it is rebuilt from scratch — and the two versions disagree.
What interviews test
Conditional aggregation, defining a metric precisely, and being asked to walk through how a figure was derived.
Practice built from business analyst work
8 exercises using the situations above, ordered from most approachable to hardest.
- IntermediateMath Functions
Total sales for a region and month with SUMIFS
Apply two conditions at once to a conditional sum.
SUMIFS
9 min25 points - IntermediateStatistical Functions
Count orders meeting two conditions
Count rows that satisfy several criteria at once.
COUNTIFS
8 min25 points - IntermediateLogical Functions
Band orders by size with nested IF
Return one of three labels depending on where a value falls.
IF
9 min25 points - AdvancedLookup & Reference
Lock ranges so a lookup survives fill-down
Use absolute references so a lookup does not break when copied down a column.
XLOOKUP
12 min40 points - AdvancedLookup & Reference
Handle missing lookups gracefully
Return a readable message instead of #N/A when a lookup finds nothing.
XLOOKUP
10 min40 points - AdvancedData Analysis
Name the second-best rep with LARGE and INDEX
Combine ranking with a lookup to return a name, not just a number.
LARGE · INDEX · MATCH
14 min40 points - AdvancedData Analysis
Build a regional summary from raw orders
Produce a summary line combining count, total and average for one region.
COUNTIF · SUMIF
16 min40 points