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

Excel VLOOKUP Practice Problem.xlsm

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