Excel
All Top 50 Excel & Business Modeling Questions#4
Performing Left Lookups Without Restructuring Tables
EasyMcKinseyInterview Question #4
Asked at McKinseyIn an ERP export, Customer Email is in Column C, while Customer ID is in Column A. In cell `F2`, a user supplies an email address. Write a formula in cell `G2` to retrieve the Customer ID in Column A without rearranging the raw columns.
Input Table: CustomerExport (A1:C4)
3 rows preview| CustomerID (Col A) | Customer Name (Col B) | Email (Col C) |
|---|---|---|
| CUST-001 | Acme Corp | orders@acme.com |
| CUST-002 | Globex Inc | info@globex.org |
| CUST-003 | Initech LLC | billing@initech.net |
Expected Output Structure1 rows
| Search Email (F2) | Customer ID (G2) |
|---|---|
| info@globex.org | CUST-002 |
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
G2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | CustomerID (Col A) | Customer Name (Col B) | Email (Col C) | |||||||
| 2 | CUST-001 | Acme Corp | orders@acme.com | info@globex.org | Target [Enter Formula] | |||||
| 3 | CUST-002 | Globex Inc | info@globex.org | |||||||
| 4 | CUST-003 | Initech LLC | billing@initech.net | |||||||
| 5 | ||||||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
Click on target cell G2 and enter your formula above.Shortcut: Click "Load Solution" to inspect