# Capstone: analyst interview test (Excel lesson 45 of 45)

> The three tasks that show up in almost every analyst Excel test.

Source: https://spreadsheetpractice.com/learn/capstone-analyst-interview · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Capstones"
- **Time:** about 12 minutes, 3 graded exercises
- **Functions:** `XLOOKUP`, `IF`, `AND`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=XLOOKUP(C2,$I$2:$I$6,$J$2:$J$6)`: Price lookup

## 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

| Formula | What it does |
|---|---|
| `=XLOOKUP(C2,$I$2:$I$6,$J$2:$J$6)` | Price lookup |
| `=D2*E2` | Line total |
| `=IF(AND(D2>=10,F2>=500),"Priority","")` | Flag |

## Watch out for

- Forgetting $ on the lookup table is the most common interview mistake.
- Return "" for a blank, not a space.

## The exercises

Solve them in the live grid at [Capstone: analyst interview test](https://spreadsheetpractice.com/learn/capstone-analyst-interview). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Price lookup.** In E2, look up each SKU's unit price from the list in I2:J6. Fill down to E9.
2. **Line totals.** In F2, calculate quantity × unit price. Fill down to F9.
3. **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.

Previous: [Capstone: sales report](https://spreadsheetpractice.com/learn/capstone-sales-report)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
