VLOOKUP Practice Example File [With Solutions] (2024)

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 tofind 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:

InTable 1we have a data table with an Employee ID, Last Name, & First name field. Let’s say that we receivedTable 2from 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 intoTable 2, however, there would be two things that would prevent you from wanting to do this:

1. The solution is manual and therefore time-consuming
2. 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.

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! - Ifyour 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'sCol_Index_Numis 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.

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.

• HLOOKUP
• XLOOKUP (New!)
• INDEX/MATCH

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