How to use a VLOOKUP in Excel

The VLOOKUP function is a powerful way of looking up data from one table and pulling it into another, this is useful for all sorts of tasks in where combining various data tables into one is required.  If you are new to the VLOOKUP function in Excel read on for an overview of how to use it…

 

What is the VLOOKUP Function?

The VLOOKUP function takes the format:

=VLOOKUP(Lookup_Value, Table_Array, Col_Index_Num, [Range_Lookup])

This is where Lookup_Value refers to the unique value you want to look up in a table, a reference number for example.  The Table_Array refers to the range of cells that contains the data you want to return back and the Col_Index_Num refers to the column number that contains the value you require populated.  The Range_Lookup is set to either FALSE for an exact match, or TRUE for an approximate match.  In my experience this will set this to FALSE the majority of occasions as its more common, and useful, to have an exact match.

To an Excel novice some of these values might sound a bit complicated but basically we are telling Excel something along the lines of “Look up this value, in this table and if you find it return the value from column X in the table”.

The VLOOKUP function in practice

Understanding how a VLOOKUP works is always best done through example so here is a basic one to get you understanding the concept.  Keep in mind that a VLOOKUP is at its most powerful when you are dealing with much bigger data tables than the ones in the example but the concept of how you implement a VLOOKUP remains the same whatever the size of data.

Example

You can download a copy of the Excel file to follow through the example here.

In this example you are given a table containing sales volumes, by region, for 5 employees in the company, the problem is you only have their employee number and their name has not been captured:

00053_VLOOKUP Basics_01

 

The employee numbers are of little use for your analysis so you get onto the companies human resources team who supply you a master list of all employees in the company, along with their employee number (found on the Employee Lookup worksheet in the download file):

00053_VLOOKUP Basics_02

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As an analyst you realise you have 2 choices here, you can either manually search the lookup table for the employees on your report and find their names or you can use the VLOOKUP function which will take out the manual searching and do the task for you.  Manual work is something to avoid like the plague when it can be automated through formula so a VLOOKUP it is!

Now is a good time to remind ourselves of the VLOOKUP function which takes the format:

=VLOOKUP(Lookup_Value, Table_Array, Col_Index_Num, [Range_Lookup])

So for the example the following is needed:

Lookup_Value – This is the employee number from your report.  It is the unique value on your report and the aim is to lookup this employee number and match it in the Employee Lookup table you have.

Table Array – This is the Employee Lookup Table cell range.  First you tell Excel to lookup the employee number and this is the part that tells Excel where to look for it.  You have to ensure here that the cell range you use contains all the fields you might need from that table, so in this case we want column A and column B as the Employee name is required from the table.

Col_Index_Num – This will be column 2 of the Table Array.  It is column 2 as the table contains 2 columns, the first  as it contains the Employee Name and that is what needs to be returned.

[Range_Lookup] – This will be FALSE because it needs to be an exact match on employee number to return the employee name.

Use the VLOOKUP function to populate the report

Once you know what you want to get and from where it’s time to write the formula.  In this example the first name to find is in cell B5 so that is where to go first.

In cell B5 you would use:

=VLOOKUP(A5,’Employee Lookup’!$A$4:$B$24,2,FALSE)

Take notice of the dollar ($) signs around the cell range for the Table_Array section ‘Employee Lookup’!$A$4:$B$24 these ensure that the cell range stays fixed so it is possible to copy down the formula in the report.  If you fail to include the dollar signs what you will find is that when you drag the formula down it will lose sync with the look up table and might cause errors.

So the formula in cell B5 should look like:

00053_VLOOKUP Basics_03

 

And your result should be Peter Parker, I guess it’s always handy to have Spider-Man working for your company!

00053_VLOOKUP Basics_04

 

To complete the report we can simply drag down the formula for the remaining missing names, do so by making sure cell B5 is selected and then drag the cell down to B9 from the bottom right hand corner:

00053_VLOOKUP Basics_05

 

You will end up with the table populated with employee names and you have achieved your goal:

00053_VLOOKUP Basics_06

 

Summary

The example provided scratches the surface of the power of the VLOOKUP function, you might be thinking you could just look the names up quicker and not have a need for formula, when you only have a few names you might be right.

It is when your report needs to look up hundreds or thousands of records from a table containing thousands of records that this function becomes a true time saver but either way it is a function that I have used regularly for a long time and one I feel every Excel analyst worth their salt should be able to implement…welcome to the VLOOKUP club!

If you have struggled to get the right result a final version of the file, populated with formulas, can be downloaded here.

Keep Excelling,

Leave a Comment