Excel
All Top 50 Excel & Business Modeling Questions#49
Extracting Targeted Column Projections with CHOOSECOLS in Modern Excel
EasyGoogleInterview Question #49
Asked at GoogleYou have a wide master table in `A2:G100`. In a presentation summary sheet, you only want to display Column 1 (Employee Name), Column 3 (Department), and Column 7 (Performance Rating). Write a single formula in `I2` using CHOOSECOLS to spill these three specific columns.
Input Table: MasterStaff (A1:G3)
2 rows preview| Name (1) | SSN (2) | Dept (3) | Salary (4) | Office (5) | Band (6) | Rating (7) |
|---|---|---|---|---|---|---|
| Alice | *** | Engineering | 120000 | SF | L5 | Exceeds |
| Bob | *** | Marketing | 85000 | NY | L4 | Meets |
Expected Output Structure2 rows
| Name (Col I) | Dept (Col J) | Rating (Col K) |
|---|---|---|
| Alice | Engineering | Exceeds |
| Bob | Marketing | Meets |
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
I2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Name (1) | SSN (2) | Dept (3) | Salary (4) | Office (5) | Band (6) | Rating (7) | |||
| 2 | Alice | *** | Engineering | 120000 | SF | L5 | Exceeds | Target [Enter Formula] | ||
| 3 | Bob | *** | Marketing | 85000 | NY | L4 | Meets | |||
| 4 | ||||||||||
| 5 | ||||||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
Click on target cell I2 and enter your formula above.Shortcut: Click "Load Solution" to inspect