Excel
All Top 50 Excel & Business Modeling Questions#2
Classic VLOOKUP with Exact Match and Column Indexing
EasyPwCInterview Question #2
Asked at PwCYou have an employee master list in `A2:D50` where Column A is Employee ID, Column B is Department, Column C is Office, and Column D is Salary. Write a VLOOKUP formula in cell `F2` to retrieve the Salary for Employee ID entered in `E2`. Ensure exact matching.
Input Table: EmployeeMaster (A1:D4)
3 rows preview| EmpID (Col A) | Department (Col B) | Office (Col C) | Salary (Col D) |
|---|---|---|---|
| 1001 | Finance | New York | 95000 |
| 1002 | Operations | London | 72000 |
| 1003 | Marketing | Singapore | 88000 |
Expected Output Structure1 rows
| Search EmpID (E2) | Returned Salary (F2) |
|---|---|
| 1002 | 72000 |
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 | EmpID (Col A) | Department (Col B) | Office (Col C) | Salary (Col D) | ||
| 2 | 1001 | Finance | New York | 95000 | Target [Enter Formula] | |
| 3 | 1002 | Operations | London | 72000 | ||
| 4 | 1003 | Marketing | Singapore | 88000 | ||
| 5 | ||||||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect