Excel VLOOKUP Exercises: 10 Tricky Problems

In this article are ten Excel VLOOKUP exercises to practice. Most of the problems are easy to solve, however you will need some intermediate level Excel knowledge to solve the last problem.

To solve the problems you will need to know about the following: 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.

These problems can be solved using Excel in any version from 2007 onwards.

Download Practice Workbook

Problem Overview

The exercises described below are in the Excel file above. There are two sheets in the file; “Problem” and “Solution”. Use the “Problem” sheet to try to solve the problems by yourself.

The image below shows the first two problems from the Excel file. We have altered this dataset slightly for most of the exercises. For the last problem, press Alt+F11 to bring up the VBA window and go through the VBA code that was used to solve it.

Problem 1 – Find Salary of an Employee

• Find the salary of “Ross” from the dataset. The following animated image shows the formula to solve this problem.

Problem 2 – Find Salary and Department of an Employee

• Return the salary and the department separated with a comma.

Problem 3 – VLOOKUP with Partial Match

• Find the salary of the employee starting with “Ro”.

Problem 4 – Return Value from a Range

• The periodic payment (PMT) values are given for a range of values. Calculate the PMT value for a range.

Problem 5 – Group Ages in Range

• The age is divided into five groups. Sort the ages into the groups.

Problem 6 – 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 7 – 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 8 – Find Duplicates

• There are two columns containing state names. Find the duplicate state names using the VLOOKUP function.

Problem 9 – Conditional Formatting with VLOOKUP

• There are two columns that contain marks from an exam. Students who got low marks could opt to retake the exam. Highlight the students that retook the exam and increased their marks.

Problem 10 – Use VBA VLOOKUP Function

• The final task is to use the VBA VLOOKUP function to find the salary of Tamara.

The following image shows a sample from the “Solution” sheet of the Excel file.

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