Excel
All Top 50 Excel & Business Modeling Questions#38
Replacing Fragile VLOOKUPs with Power Pivot Data Model Relationships
MediumAmazonInterview Question #38
Asked at AmazonYou have a 1-million row Sales Fact Table and a Product Dimension Table. A junior analyst writes 1,000,000 VLOOKUP formulas to pull Product Category into the sales table before making a Pivot Table, blowing the file size up to 250MB and freezing Excel. Explain how Power Pivot Data Model relationships solve this.
Input Table: StarSchemaArchitecture
2 rows preview| Table Type | Table Name | Key Column |
|---|---|---|
| Fact Table | Sales_Fact (1,000,000 rows) | ProductID (Foreign Key) |
| Dimension Table | Product_Dim (5,000 rows) | ProductID (Primary Key) |
Expected Output Structure2 rows
| Architecture | File Size | Calculation Speed |
|---|---|---|
| 1 Million VLOOKUP Formulas | 250MB+ | Sluggish, 2-minute recalculation |
| Power Pivot Data Model Relationship | 15MB (xVelocity compression) | Instantaneous Pivot Table filtering |
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 | Table Type | Table Name | Key Column | |||
| 2 | Fact Table | Sales_Fact (1,000,000 rows) | ProductID (Foreign Key) | Target [Enter Formula] | ||
| 3 | Dimension Table | Product_Dim (5,000 rows) | ProductID (Primary Key) | |||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect