VLOOKUP Practice Example File [With Solutions]
What Is Vlookup?
Vlookup stands for Vertical Lookup and is part of a group of functions that I like to call “Lookup Functions”. A Lookup Function’s sole purpose is to pull in information from a table of data based on a unique identifier (I will refer to these as “IDs”).
It’s like telling your dog to find your yellow ball (Lookup Value ID) and that it’s somewhere in the backyard (Table Array). Okay, give your dog a little more help because you have a REALLY BIG backyard!
You then specify it’s on the left side of the backyard (Column Reference) and that he has to bring back the exact ball you asked for (Range Lookup).
Kind of make sense? For a simple spreadsheet-based example let’s look at the data below:
In Table 1 we have a data table with an Employee ID, Last Name, & First name field. Let’s say that we received Table 2 from the Payroll department and we need to fill in the last name associated with the Employees ID.
With the small amount of data in this example, you probably could get away with manually looking up each person’s ID number and copying/pasting their last name into Table 2, however, there would be two things that would prevent you from wanting to do this:
- The solution is manual and therefore time-consuming
- You are keying in data by hand which should always be avoided if possible
Plus, what if you had to fill in the last names of a thousand employees? This would turn into an all-day exercise! Luckily we can use an Excel Lookup function to do this search for us. Watch the below animation to see how quickly I can use the VLOOKUP function to pull in the data I want.
VLOOKUP Function Inputs
There are 3 required and 1 optional input in the VLOOKUP function. The below table describes all four inputs.
|Lookup_Value||Required||This is your ID. The text or number that is unique to the line item you are searching for and is not repeated anywhere else in your data set. This value must be located in the FIRST column of your Table_Array.|
|Table_Array||Required||The range of your table that you want to retrieve data from. The one requirement to this range is that your ID column must be the first column in the range.|
|Col_Index_Num||Required||A number input that references the column you want your formula to search in.|
|Range_Lookup||Optional||This input is technically optional but Microsoft chose to make the default the option that no one uses, so I’m saying it’s required because 99% of the time you are NOT going to use the default (I don’t think I ever have). Anyways….this option tells the Vlookup function whether you want it to find an exact match or make a guess (the default) when trying to match up your ID value (Lookup_Value). If you have your unique ID setup correctly you will have no need to have the Vlookup make a typically inaccurate guess. So just make it easy on yourself and ALWAYS MAKE THIS FALSE or you can also use the number zero (0 stands for false in binary code) instead.|
Possible Errors That Can Occur
There are 3 different errors that can occur if your VLOOKUP function cannot find a match or is set up improperly.
- #REF! – If your function’s Col_Index_Num is larger than the number of columns in your Table_Array, your VLOOKUP function will return a #REF! error.
- #VALUE! – If your function’s Col_Index_Num is less than 1, your VLOOKUP function will return a #VALUE! error.
- #N/A – If you input FALSE (or 0) for your Range_Lookup parameter and no exact match can be found, your VLOOKUP function will return a #N/A error. You can hand this by wrapping an IFERROR function around your VLOOKUP function.
VLOOKUP Function Practice Examples
Here is an Excel file you can download to see ways you can apply the VLOOKUP Function in your spreadsheets!
There are both working tabs and solution tabs provided within the Excel file so you can reference the answers if you can’t solve the task on the first try.
Example 1: Add First/Last Name From Another Table
In this practice example, you are asked to add the first and last name of the employee to a Pay Report. You will need to use the Employee ID to Vlookup the name columns from another table so you don’t have to manually type out all the names.
Example 2: Vlookup From Multiple Tables
In this practice example, you will need to reference two separate data table sources in order to complete the requested Pay Report.
Example 3: Vlookup Using Approximate Matching
In this practice example, you will need to utilized VLOOKUP’s approximate match capability to categorize each employee to their proper payroll pay band based on the amount they are being paid.
Why You Should Learn VLOOKUP
I currently sit right across from the Human Resources department and I always find myself listening in on the questions that our hiring managers ask prospective hires over the phone. Over time I recognized that certain questions were always asked to size up abilities pertaining to their analytic abilities. I found it really fascinating that computer skills (especially Excel) could be analyzed by one very simple question: Do you have experience using VLOOKUP?
VLOOKUP seems to be that one function that basic users (including myself at one point) have never heard of and that even recognizing the function’s name puts you into a category of an “experienced Excel user”. While I can agree that VLOOKUP is an essential function to know, I probably would not categorize all people who know how to use it as experienced users, but it is definitely a stepping-stone towards becoming one.
Understanding how to use VLOOKUP was the pinnacle moment in my Excel experience where I realized that there was way more to spreadsheets than adding and multiplying numbers. Hopefully, the information on this page has helped you get a grasp of what Vlookup is and how it can be used.
Other Lookup Functions To Learn
Microsoft Excel has additional Lookup functions that you can use within your spreadsheets. Each function has its own pros and cons.
- XLOOKUP (New!)
XLOOKUP was released in 2020 and was created to be an all-in-one solution for lookup needs. This is the function I would recommend you learn next as it has the most diversity in its use cases.
You can learn more about this function in my dedicated XLOOKUP guide which will teach you everything you need to know.
Additional Vlookup Resources
- The Vlookup Formula: Why Your Employer Wants you to Know How to Use It
- VLOOKUP Explained in Simple Terms at Starbucks
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Why Does Excel Say Cell Values Are Not Equal When They Are?
What's Wrong With Excel? I try to get on the Mr. Excel forums a few times a week and this...
My First Microsoft Excel MVP Award!
UPDATE: In July 2017, I received my second MVP award from Microsoft! Thank you for supporting this website and continuing...
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.