Excel
All Top 50 Excel & Business Modeling Questions#41
Dynamic Record Extraction with the Modern FILTER Array Function
MediumGoogleInterview Question #41
Asked at GoogleYou have a sales table in `A2:D50` with Rep in Col A, Region in Col B, Deal Size in Col C, and Status in Col D. In cell `F2`, write a single formula using FILTER to return all columns for deals where Region is 'West' AND Deal Size >= $50,000.
Input Table: DealsLedger (A1:D4)
4 rows preview| Rep (Col A) | Region (Col B) | Deal Size (Col C) | Status (Col D) |
|---|---|---|---|
| Sarah | West | 75000 | Won |
| David | East | 120000 | Won |
| Alex | West | 30000 | Pending |
| Elena | West | 95000 | Won |
Expected Output Structure2 rows
| Rep | Region | Deal Size | Status |
|---|---|---|---|
| Sarah | West | 75000 | Won |
| Elena | West | 95000 | Won |
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 | Rep (Col A) | Region (Col B) | Deal Size (Col C) | Status (Col D) | ||
| 2 | Sarah | West | 75000 | Won | Target [Enter Formula] | |
| 3 | David | East | 120000 | Won | ||
| 4 | Alex | West | 30000 | Pending | ||
| 5 | Elena | West | 95000 | Won | ||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect