Vlookup Excel Function

Overview

Vlookup Formula Inputs
InputNeeded?Description
Lookup_ValueRequiredThis 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_ArrayRequiredThe 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_NumRequiredA number input that references the column you want your formula to search in.
Range_LookupOptionalThis 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

  • 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.
  • If your function's Col_Index_Num is less than 1, your VLOOKUP function will return a #VALUE! error.
  • 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.

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”).  Its kind of like telling your dog to find your yellow ball (Lookup Value ID), that it's somewhere in the backyard (Table Array).  Okay,give your dog  a little more help because you have a REALLY BIG back yard!  You then specify its on the left side of the back yard (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 copy/pasting there last name into Table 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 avoid if possible

Plus, what if you had to fill in the last names for 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.

Examples

FormulaOutput
=VLOOKUP(165852,$A$1:$D$7,3,FALSE)Pink
=VLOOKUP(132543,$A$1:$D$7,4,0)50
=VLOOKUP(A4,$A$1:$D$7,2,FALSE)Crayon
=VLOOKUP(165852,$A$1:$D$7,5,FALSE)#REF!
=VLOOKUP(165852,$B$1:$D$7,3,FALSE)#N/A
=VLOOKUP(161352,$A$1:$D$7,3,FALSE)#N/A

Here is an Excel file you can download to see ways you can apply the Vlookup Function in your spreadsheets!

An Analytic Standard

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 then 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.

Further Resources

Links

The Vlookup Formula: Why Your Employer Wants you to Know How to Use It (And What You Can Do About It)

VLOOKUP Explained in Simple Terms at Starbucks