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.

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 following image shows a sample from the “Solution” sheet of the Excel file.

Related Articles

Get FREE Advanced Excel Exercises with Solutions!

Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

Polepeddi Sasidhar May 1, 2023 at 10:17 PM

I like these Excel problems.

2. Hi,

• Hello Balaji,

All the solutions are available in the Solution sheet of the Excel Workbook. Excel file is given in the Download practice Workbook section.

All the solutions are given part by part in the Solution sheet.

Regards
ExcelDemy

Advanced Excel Exercises with Solutions PDF