Excel
All Top 50 Excel & Business Modeling Questions#5
Multi-Criteria Lookup Using Boolean Array Multiplication
MediumKPMGInterview Question #5
Asked at KPMGYou have a salary table in `A2:D50` with Department in Col A, Role in Col B, Level in Col C, and Base Salary in Col D. Given target Department in `F2` and target Role in `G2`, write a single modern formula in `H2` to return the Base Salary matching both conditions.
Input Table: SalaryBands (A1:D4)
3 rows preview| Dept (Col A) | Role (Col B) | Level (Col C) | Base Salary (Col D) |
|---|---|---|---|
| Analytics | Data Analyst | L1 | 70000 |
| Analytics | Senior Analyst | L2 | 95000 |
| Engineering | Data Analyst | L1 | 85000 |
Expected Output Structure1 rows
| Target Dept (F2) | Target Role (G2) | Base Salary (H2) |
|---|---|---|
| Analytics | Senior Analyst | 95000 |
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
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Dept (Col A) | Role (Col B) | Level (Col C) | Base Salary (Col D) | ||
| 2 | Analytics | Data Analyst | L1 | 70000 | Target [Enter Formula] | |
| 3 | Analytics | Senior Analyst | L2 | 95000 | ||
| 4 | Engineering | Data Analyst | L1 | 85000 | ||
| 5 | ||||||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect