Excel VLOOKUP Exercises: 10 Tricky Problems

In this article, you will get ten Excel VLOOKUP exercises to practice. Most of the problems are easy to solve. However, you will need a little bit of intermediate level Excel knowledge to solve the last problem. To solve the problems in this article, you will need to know about the following things: the VLOOKUP and COUNTIF functions, returning multiple values separated by commas, finding partial matches, calculating down payments, group age ranges, returning the second match, nested VLOOKUP, finding duplicate matches, conditional formatting, the VBA VLOOKUP function, and best practices for the VLOOKUP. Additionally, you can solve these problems using Excel in any version starting with 2007.

Download Practice Workbook

You can download the Excel file from the following link.

Problem Overview

There are ten exercises related to the VLOOKUP function in this Excel file. We have slightly altered the problem dataset for most of the exercises. There are two sheets on the file; “Problem” and “Solution”. You will use the “Problem” sheet to try to solve the problems by yourself. Moreover, you can press Alt+F11 to bring up the VBA window and go through the VBA code that was used to solve the last problem. The image below shows the first two problems from the Excel file.

• Problem 01 Find Salary of an Employee: You will need to find the salary of “Ross” from the dataset. The following animated image shows the formula to solve this problem.

• Problem 02 Find Salary and Department of an Employee: Your task is to return the salary and the department separated with a comma.
• Problem 03 VLOOKUP with Partial Match: You will find the salary of the employee starts with “Ro”.
• Problem 04 Return Value from a Range: The periodic payment (PMT) values are given for a range of values. Your task is to calculate the PMT value for a range.
• Problem 05 Group Ages in Range: The age is divided into five groups. Your task is to sort the ages into the groups.
• Problem 06 Return Second Match: In this problem, a name has repetitions. Your goal is to find the value corresponding to the second occurrence of that name. Moreover, use a helper column to count the instances of the salesman.
• Problem 07 Nested VLOOKUP: Use the nested VLOOKUP to return values from two related tables. The second column from the first table is the first column in the second table. Use this to find the price of the A101 ID.
• Problem 08 Find Duplicates: There are two columns containing the state names. Find the duplicate state names using the VLOOKUP function.
• Problem 09 Conditional Formatting with VLOOKUP: There are two columns that contain marks from an exam. Then, students who got low marks can opt for a retake examination. Finally, highlight the students that took a retake exam and increased marks.
• Problem 10 Use VBA VLOOKUP Function: The final task is to use the VBA VLOOKUP function to find the salary of Tamara.

Now, the following image shows the “Solution” sheet of the Excel file.

Conclusion

Thank you for reading this article. We hope you have learned about Excel VLOOKUP by solving these exercises. Additionally, you can find more articles similar to this on the ExcelDemy website. If you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Keep excelling!

Related Articles

• VLOOKUP Not Working (8 Reasons & Solutions)
• Excel LOOKUP vs VLOOKUP: With 3 Examples
• What Is Table Array in Excel VLOOKUP?
• Why VLOOKUP Returns #N/A When Match Exists (with Solutions)
• How to Use Nested VLOOKUP in Excel (3 Criteria)
• Make VLOOKUP Case Sensitive in Excel (4 Methods)
• How to VLOOKUP and SUM Across Multiple Sheets in Excel