Excel
All Top 50 Excel & Business Modeling Questions#3
Two-Way Matrix Lookup with INDEX and MATCH
MediumEYInterview Question #3
Asked at EYYou have a regional shipping rate matrix in `B2:F10`. Rows in Column A represent Weight Categories (e.g. '0-5kg', '5-10kg'), while Columns B through F represent Zones ('Zone 1' through 'Zone 5'). Given a target Weight in `H2` and a target Zone in `H3`, write a formula in `H4` to return the intersecting rate.
Input Table: ShippingMatrix (A1:D4)
3 rows preview| Weight (Col A) | Zone 1 (Col B) | Zone 2 (Col C) | Zone 3 (Col D) |
|---|---|---|---|
| 0-5kg | 15 | 22 | 30 |
| 5-10kg | 25 | 38 | 52 |
| 10-20kg | 45 | 65 | 85 |
Expected Output Structure1 rows
| Target Weight (H2) | Target Zone (H3) | Rate (H4) |
|---|---|---|
| 5-10kg | Zone 2 | 38 |
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 | Weight (Col A) | Zone 1 (Col B) | Zone 2 (Col C) | Zone 3 (Col D) | ||
| 2 | 0-5kg | 15 | 22 | 30 | Target [Enter Formula] | |
| 3 | 5-10kg | 25 | 38 | 52 | ||
| 4 | 10-20kg | 45 | 65 | 85 | ||
| 5 | ||||||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect