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
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 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 | ||||||||||
| 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.