Lesson 29 of 29, Capstones
Capstone: analyst interview test
The three tasks that show up in almost every analyst Excel test.
Watch it done
4 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
Hiring tests for analyst, operations and finance roles lean on the same handful of skills: a lookup, a calculated column and a conditional flag. Interviewers watch for locked lookup ranges and one formula filled down, not a separate formula per row.
Give yourself a clock: aim to finish all three in under 10 minutes.
Syntax
=XLOOKUP(C2,$I$2:$I$6,$J$2:$J$6)Price lookup=D2*E2Line total=IF(AND(D2>=10,F2>=500),"Priority","")FlagWatch out for
- Forgetting $ on the lookup table is the most common interview mistake.
- Return "" for a blank, not a space.
The exercises
- Price lookup. In E2, look up each SKU's unit price from the list in I2:J6. Fill down to E9.
- Line totals. In F2, calculate quantity × unit price. Fill down to F9.
- Priority flag. In G2, show Priority when quantity is at least 10 and the line total is at least $500; otherwise leave it blank (""). Fill down to G9.
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.
- Price lookup
- Line totals
- Priority flag