Cheat sheet
Excel for analyst interviews
Lookups
| Function | Syntax | Remember |
|---|---|---|
| VLOOKUP | =VLOOKUP(key, table, col_num, FALSE) | Searches the 1st column, returns col_num to the right. The default is approximate. It can't look left. |
| HLOOKUP | =HLOOKUP(key, table, row_num, FALSE) | Searches the 1st row, returns row_num down. |
| MATCH | =MATCH(key, range, 0) | Returns a position. 0 = exact. The default 1 = approximate. |
| INDEX | =INDEX(range, row, [col]) | Returns the value at a position. |
| INDEX+MATCH | =INDEX(return_rng, MATCH(key, key_rng, 0)) | Any direction. Survives inserted columns. Two-way with 2 MATCHes. |
| XLOOKUP | =XLOOKUP(key, look_rng, return_rng, "Not found", 0, 1) | Exact by default. Any direction. search_mode −1 = last match. Not in Excel 2016/2019. |
Approximate match (TRUE / 1) needs the key column sorted ascending and returns the largest value ≤ key. Use it for bands (grades, tax slabs).
Core formulas
| Formula | Note |
|---|---|
SUMIF(range, crit, sum_range) | Sum range last |
SUMIFS(sum_range, crit_rng1, crit1, …) | Sum range first |
COUNTIFS(crit_rng1, crit1, …) · AVERAGEIFS(avg_rng, …) | Multi-condition count / average |
COUNT / COUNTA / COUNTBLANK | numbers / non-empty / empty |
IF(test, yes, no) · IFS(...) | Branching |
IFERROR(value, fallback) | Catches any error. It can hide real bugs. |
TRIM LEFT RIGHT MID LEN & CONCAT | Text cleaning and joining |
$A$1 absolute · A$1/$A1 mixed · A1 relative | F4 (⌘T on Mac) cycles through them |
Pivot Tables
Build
Clean data (1 header row, no blanks) → Ctrl+T → Insert → PivotTable → drag fields into Rows · Columns · Values · Filters.Must-knows
Value Field Settings (Sum/Count/Avg) · Show Values As → % of Grand Total · Group dates · Slicers · Refresh after source changes.Each value cell = a SUMIFS with the row and column labels as criteria. Sources: MS: lookups · MS: XLOOKUP · MS: PivotTable