Excel VLOOKUP Exercises: 10 Tricky Problem
This article provides you with ten Excel VLOOKUP exercises to practice. Most of the problems are straightforward to solve, though one requires intermediate Excel knowledge. In order to tackle these tasks, you’ll need knowledge about various concepts like VLOOKUP and COUNTIF functions; returning multiple values separated by commas; finding partial matches; calculating down payments; group age ranges; returning second match; nested VLOOKUP finding duplicate matches; conditional formatting using VBA VLOOKUP function; as well as best practices using VLOOKUP in any version since 2007.
Download Practice Workbook
You may download the Excel file by following this link.
Excel VLOOKUP Practice Problem
Problem Overview
This Excel file contains ten exercises related to the VLOOKUP function. We’ve slightly altered some of the problem datasets for most of these exercises. There are two sheets on the file; “Problem” and “Solution”, so use that sheet as your starting point when solving these problems yourself. Moreover, pressing Alt+F11 will open a VBA window where you can examine the code used to solve each last problem – see image below).
- Exercise 1 – Determine the Salary of an Employee: From this dataset, you must find the salary for “Ross”. The animated image below demonstrates how to solve this problem using formulae.
- Exercise 02 Determine Salary and Department of an Employee: Your task is to return the salary and department separated with a comma.
- Exercise 03 Perform VLOOKUP with Partial Match: You will find that the employee’s salary begins with “Ro”.
- Exercise 04 Return Value from a Range: Given an array of values, your task is to calculate the periodic payment (PMT) value for each range.
- Exercise 05 Group Ages in a Range: Divide the age into five groups and sort each age into its appropriate group.
- Exercise 06 Return Second Match: In this problem, a name appears multiple times. Your goal is to find the value corresponding to each additional occurrence of that name using a helper column. Furthermore, count up all instances of the salesman in both columns.
- Exercise 07 Nested VLOOKUP: Use the nested VLOOKUP to retrieve values from two related tables. The second column from the first table corresponds to column one in the second table; use this information to calculate the price for an A101 ID.
- Exercise 08 Identify Duplicates: Given two columns containing state names, use the VLOOKUP function to identify any duplicates.
- Exercise 09 Conditional Formatting with VLOOKUP: Two columns contain marks from an exam. Students who got low marks can choose to retake the examination; finally, highlight those students who took a retake exam and improved their marks.
- Exercise 10 – Utilize VBA VLOOKUP Function: Finally, we must utilize the VBA VLOOKUP function to determine Tamara’s salary.
The following image displays the “Solution” sheet from an Excel file.
Conclusion
Thank you for reading this article. We hope that by solving these exercises, you have acquired some understanding of Excel VLOOKUP. For further assistance or questions, feel free to leave a comment below; however, keep in mind our website uses comment moderation which may cause your comment not to appear immediately. So please be patient and our team will do their best to resolve your query quickly and accurately. Keep excelling!
Discover Exciting New ChatGPT Posts That Will Blow Your Mind – Click Here!
Practice VLOOKUP Example File [With Solutions]
What Is Vlookup?
Vlookup stands for Vertical Lookup and belongs to a group of functions I like to refer to as “Lookup Functions”. A Lookup Function’s sole purpose is to retrieve information from an array of data based on a unique identifier (I will refer to these as “IDs”), taken from some table somewhere.
Imagine telling your dog to look for the yellow ball (Lookup Value ID), which is somewhere in the backyard (Table Array). Now give him some extra help since your backyard is really big!
You then specify it’s on the left side of your backyard (Column Reference) and that he must bring back exactly the ball you asked for (Range Lookup).
Does that make any sense? For a simple spreadsheet example, let’s examine the data below:
Table 1 presents a data table with three fields: Employee ID, Last Name, and First name. Assume we received Table 2 from Payroll and need to fill in the last name associated with an Employee’s ID.
In this example, with only a minimal amount of data, manually looking up each person’s ID number and copy/pasting their last name into Table 2 may not be necessary; however, two things would prevent you from doing this:
- The solution is laborious and therefore inefficient.
- Keying data by hand should always be avoided whenever possible.
Imagine having to fill in the last names of a thousand employees – it would take all day! Luckily, an Excel Lookup function can do this search for us. Check out the animation below to see how quickly I can use VLOOKUP to retrieve desired data.
VLOOKUP Function Inputs
The VLOOKUP function requires three mandatory and one optional input. The table below outlines these four inputs in detail.
| Input | Needed? | Description |
| Lookup_Value | Required | Your ID, also known as your reference number, is the text or number unique to each line item in your data set and cannot be repeated elsewhere. It must appear in the First column of your Table_Array data set. |
| Table_Array | Required | Your table’s range from which you wish to retrieve data should include at least your ID column as the first column. |
| Col_Index_Num | Required | Enter a number that refers to the column you wish your formula to search in. |
| Range_Lookup | Optional | Microsoft made this input optional, but most people never use it – so consider it mandatory! When using the Vlookup function to match up ID values (Lookup_Value), this option tells it whether to look for an exact match or make an educated guess (the default). With your unique ID set correctly and no need for Vlookup to make inaccurate guesses, simply make this field False (zero is false in binary code) instead. |
Potential Errors That Could Occur
If your VLOOKUP function cannot find a match or is configured incorrectly, three distinct errors can occur.
#REF! – If the Col_Index_Num of your function is greater than the number of columns in your Table_Array, your VLOOKUP function will return an error with code #REF!.
#VALUE! – If the Col_Index_Num of your function is less than 1, then VLOOKUP will return an error code of #VALUE!.
#N/A Error Message – If your Range_Lookup parameter is set to FALSE (or 0) and no exact match can be made, your VLOOKUP function will return an “N/A.” To resolve this, wrap an IFERROR function around your VLOOKUP function for extra assurance.
VLOOKUP Function Practice Examples
Download our Excel file to explore ways you can apply the VLOOKUP Function in your spreadsheets!
Excel provides both working tabs and solution tabs, so if you can’t solve a task on the first try, you can refer to the answers for assistance.
Download Example File Here
Example 1: Add First/Last Name From Another Table
In this practice example, you are required to add the employee’s first and last names to a Pay Report using their Employee ID. Rather than typing out all of these names manually, use their Employee ID to Vlookup the name columns from another table so you don’t have to.
Example 2: Vlookup From Multiple Tables
To complete this practice example, you will need to reference two different data table sources in order to generate your Pay Report.
Example 3: VLOOKUP with Approximate Matching
For this practice example, you will use VLOOKUP’s approximate match capability to categorize each employee according to their payroll pay band based on how much they are being paid.
Why You Should Learn VLOOKUP
Since my office is directly adjacent to Human Resources, I often observe the questions our hiring managers ask prospective hires over the phone. Over time I noticed certain questions were always designed to assess an applicant’s analytic skillsets. It struck me as remarkable that computer skills (especially Excel) could be gauged with just one simple question: Have you used VLOOKUP before?
VLOOKUP seems to be one of those functions that many basic Excel users (including myself at one point) have never heard of, yet even knowing its name puts you into the realm of an “experienced Excel user”. While VLOOKUP is undoubtedly useful and should be known, I wouldn’t label those who know how to use it as experienced users – however, knowing its ins and outs can definitely serve as a stepping stone towards becoming one.
Understanding VLOOKUP was the defining moment in my Excel journey when I recognized there was more to spreadsheets than just adding and multiplying numbers. Hopefully, the information provided on this page has given you a good understanding of what Vlookup is and how it can be applied.
Other Lookup Functions to Explore
Microsoft Excel offers additional Lookup functions that you can utilize within your spreadsheets. Each function has its own advantages and drawbacks; take your pick!
- HLOOKUP
- XLOOKUP (New!)
- INDEX/MATCH
Launched in 2020, XLOOKUP offers an all-inclusive solution for lookup needs. I highly recommend learning this function next since it has the most diverse set of use cases available.
Discover all about XLOOKUP in my comprehensive XLOOKUP guide, which covers everything you need to know.
