Excel
All Top 50 Excel & Business Modeling Questions#9
Consolidating Dynamic Worksheets with the INDIRECT Function
MediumKPMGInterview Question #9
Asked at KPMGA workbook has twelve monthly tabs named `Jan`, `Feb`, `Mar`, etc., each having Total Revenue in cell `B10`. On a Summary sheet, cell `A2` contains the text 'Mar'. Write a formula in cell `B2` using INDIRECT to dynamically fetch cell `B10` from the tab named in `A2`.
Input Table: Summary Sheet (A1:A3)
2 rows preview| Month Tab Name (Col A) | Total Revenue (Col B) |
|---|---|
| Jan | Fetch Jan!B10 |
| Mar | Fetch Mar!B10 |
Expected Output Structure1 rows
| Month Tab (A2) | Consolidated Revenue (B2) |
|---|---|
| Mar | 125400 |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Month Tab Name (Col A) | Total Revenue (Col B) | ||||||||
| 2 | Jan | Fetch Jan!B10 | Target [Enter Formula] | |||||||
| 3 | Mar | Fetch Mar!B10 | ||||||||
| 4 | ||||||||||
| 5 | ||||||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
This question demonstrates a technique that can't be graded as a single cell formula (it needs another sheet, a real PivotTable, or compares two methods side-by-side). Read the explanation below instead.