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.
For advanced learning of how VLookups and other concepts can be extended into real world applications – check out our free course on Automating Financial Reporting.
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.
