If you’re learning Excel, VLOOKUP is one of the most useful functions to master. It helps you quickly search for a value in a table and return related information — like finding an employee’s department, salary, or bonus just by entering their ID.
In this article, you’ll find:
- A free VLOOKUP practice exercises XLS download
- A sample dataset for hands-on learning
- 20 practice problems with solutions
By the end, you’ll be confident using VLOOKUP in real accounting, finance, or business cases.
Download the Practice File
We’ve created a workbook with data + problems + solutions that you can use to practice step by step.
📊 Sample Dataset (from the Excel file)
| EmpID | Name | Department | Location | Salary | Bonus % |
|---|---|---|---|---|---|
| 101 | Alice Wong | Finance | New York | 65,000 | 10% |
| 102 | Bob Smith | Marketing | Chicago | 72,000 | 12% |
| 103 | Carol Jones | IT | San Diego | 80,000 | 8% |
| 104 | David Chen | HR | New York | 58,000 | 9% |
| 105 | Emily Davis | Finance | Chicago | 62,000 | 11% |
| 106 | Frank Lee | Marketing | Boston | 76,000 | 10% |
| 107 | Grace Hall | IT | San Diego | 82,000 | 7% |
| 108 | Henry Adams | HR | Chicago | 55,000 | 9% |
| 109 | Irene Scott | Finance | Boston | 68,000 | 10% |
| 110 | Jack White | IT | New York | 85,000 | 12% |
📝 VLOOKUP Practice Problems
Here are 20 real-world style questions included in the workbook:
- Find the Name of the employee with EmpID = 104.
- Find the Department of employee 106.
- Find the Salary of employee 102.
- Retrieve the Bonus % for EmpID = 109.
- Find the Location of employee 101.
- Using EmpID, get the Salary of 110.
- Retrieve the Department of 103.
- Find the Name of the employee who earns 62,000.
- Find the Salary of 105 and add the Bonus % to compute total compensation.
- Lookup the Location of 107.
- Find the Bonus % of 102 and calculate the Bonus Amount.
- Return the Department for employee 108.
- Get the Name of employee 109.
- Find the Salary of the employee in Boston (Finance).
- Retrieve the Department of 101.
- Find the Bonus % of the highest-salary employee (EmpID 110).
- Lookup the Salary for 103 and apply a 5% increment.
- Find the Location of 105.
- Return the Name of employee 106.
- For EmpID 107, calculate
Salary + (Salary * Bonus %).
✅ Answer Key (Formulas & Results)
Here’s how you solve them with VLOOKUP (and a few INDEX/MATCH for advanced cases):
=VLOOKUP(104,A2:F11,2,FALSE)→ David Chen=VLOOKUP(106,A2:F11,3,FALSE)→ Marketing=VLOOKUP(102,A2:F11,5,FALSE)→ 72,000=VLOOKUP(109,A2:F11,6,FALSE)→ 10%=VLOOKUP(101,A2:F11,4,FALSE)→ New York=VLOOKUP(110,A2:F11,5,FALSE)→ 85,000=VLOOKUP(103,A2:F11,3,FALSE)→ IT- (Requires INDEX/MATCH since Salary isn’t first column) → Emily Davis
=VLOOKUP(105,A2:F11,5,FALSE)*(1+VLOOKUP(105,A2:F11,6,FALSE))→ 68,820=VLOOKUP(107,A2:F11,4,FALSE)→ San Diego=VLOOKUP(102,A2:F11,5,FALSE)*VLOOKUP(102,A2:F11,6,FALSE)→ 8,640=VLOOKUP(108,A2:F11,3,FALSE)→ HR=VLOOKUP(109,A2:F11,2,FALSE)→ Irene Scott=INDEX(E2:E11,MATCH(1,(C2:C11="Finance")*(D2:D11="Boston"),0))→ 68,000=VLOOKUP(101,A2:F11,3,FALSE)→ Finance=VLOOKUP(110,A2:F11,6,FALSE)→ 12%=VLOOKUP(103,A2:F11,5,FALSE)*1.05→ 84,000=VLOOKUP(105,A2:F11,4,FALSE)→ Chicago=VLOOKUP(106,A2:F11,2,FALSE)→ Frank Lee=VLOOKUP(107,A2:F11,5,FALSE)*(1+VLOOKUP(107,A2:F11,6,FALSE))→ 87,740
🚀 Why Practice with XLS Exercises?
Learning Excel is like learning a language: you get better by doing.
By working through real-world datasets (instead of abstract numbers), you’ll understand how to apply VLOOKUP in finance, HR, sales, or operations.
This VLOOKUP practice exercises XLS download gives you hands-on problems with step-by-step answers, so you can check your work instantly.
👉 Ready to start? Download the free practice file here and begin mastering VLOOKUP today.
