VLOOKUP a Starting Point for Excel Mastery

Chances are, if you’ve worked with Excel in a business function, you’ve had to navigate, manipulate, and summarize large sets of data.

When working with spreadsheets with large sets of data the VLOOKUP is one of the most important functions you can learn. You can use it to build a foundation of Excel knowledge. Below I am going to show you how to perform VLOOKUPs.

Let’s begin.

You may want to download the sample file by clicking here.

I’m working with UN population data for this example.

I want to create a summary of the population in North America using VLOOKUPs. 

First let’s insert a new tab in our worksheet and name it “summary”.

Right click on the tab named “Data”.

Select “Insert” from the menu.

Click OK. (The “Worksheet” option should be the default)

Now let’s rename the tab.

Right click on the worksheet labeled “Sheet1”

Select the “Rename” option

Type “Summaryy”, then click away from the tab.

Your screen should now look like this:

Let’s create a summary of the population in North America. For our purposes we’ll consider The United States of America and Canada will comprise North America.

Type the following country names in cells B2 and B3:

United States of America

Canada

Type in years in cells C1 through C11. Start with 2011 and end with 2020.

Your spreadsheet should now look like below:

Now we do a VLOOKUP to find the population of the countries for each of the years.

In cell C2 type =VLOOKUP(

Then select B2, this is the text the formula will look for and return a value based on it. In this case it’s United States of America.

Type a comma

Then go to the “Data” tab and select the whole data set from B3 to L201.

Type a comma

Type 2, this is the column of the data set which the formula will return. In this case column 2 has values for 2011.

Type a comma

Type “FALSE”, this will tell the formula to return an exact match for “United States of America”.

The finished formula should look like this: =VLOOKUP(B2,Data!B3:L201,2,FALSE)

The next step is to make sure the referencing on the formula will work if we copy in paste it in the next row, for Canada.

Click in the formula on the “B2” segment, tap the F4 key until you see $B2, this means that the formula will always reference column B, even if you copy and paste it. 

 
Then click on the”B3” segment, tap F4 until it looks like: $B$3, this will lock in the starting point for the formula’s reference range. Do the same for the “L210” segment of the formula.

Your formula should now look like this: =VLOOKUP($B2,Data!$B$3:$L$201,2,FALSE)

You should now be able to copy and paste the formula. The spreadsheet should look like this:

Copy the formula and paste it in C3 for Canada’s population in 2011. Do the same for the year 2012, notice that it didn’t work, that’s because the formula is still referencing column 2, we need to change it to 3. 

Change the formulas in column D to reference column 3, like =VLOOKUP($B2,Data!$B$3:$L$201,3,FALSE)

In later posts we’ll cover nesting a MATCH function within a VLOOKUP to completely automate the VLOOKUP function.

For now copy the formula and change the column manually in each. 2013 would be column 4 and so on.

Your ending spreadsheet should look like this:

You should now have a basic understanding of VLOOKUP formulas.