1. Finding Records with VLOOKUP

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Lookups and Database Functions

11 lessons , 3 exercises

Preview Course

Overview

VLOOKUP is a popular command for finding unique records within a dataset. Its awkward syntax does take some practice however.

Summary

  1. Finding a Salesperson (00:24)

    Our dataset relates to a software company. They have identified a set of customers who they think may end their subscription. We aim to identify the sales person for each of these companies, so they can intervene.

    We could use the FIND function, by pressing Ctrl + F. However, this is quite a manual process, as it requires us to find an individual company, then read their salesperson manually. A better approach is to use a lookup function.

  2. Using VLOOKUP (01:13)

    The VLOOKUP function identifies a unique value in a column, and returns another value in the same row. For example, we can lookup a company name, then return the corresponding salesperson.

    VLOOKUP takes four arguments. First is the value to lookup, such as the company name we are searching for. 

    Second is the table array where we can find the lookup value and the value to return. Note that the column to lookup should always be the first column in this array. 

    Third is the column index, which is the number of the column in the array where the value to be returned is found. In our example, this is 8 as sales people are in column 8 of the array. 

    Fourth is the range lookup. This is a True/False value indicating if you want an approximate match or an exact match. You’ll almost always want False for an exact match.

    VLOOKUP can be slightly complicated initially, but it’s a great way of isolating individual records using unique lookup values like company names.

Transcript

Welcome to the first lesson in our course on Lookups and Database Functions. This course will build on the work we did in the Formulas course, to provide you with the complete set of tools for manipulating and filtering data. The sales data set we're going to use in this course is for a software company with 150 customers, each who pay for the product annually. In this lesson we're going to explore how the VLOOKUP function can be used to quickly find individual entries in our data set.

This software company has a Watch list of customers, that it feels are likely to end their subscription next year. Our job is to find out which Sales person is assigned to these listed companies and to tell them to offer the client a discount. One way to complete this task would be to use the Find function with the shortcut Ctrl + F. We could then search for a company such as Blammo Corp, by pressing Enter, and once we find the entry we can then read across to find the Sales person, which in this case is Parrish. However if you've a long Watch list, this could get pretty tedious, and instead we'll use Lookups. Lookups allow us to search a column in our data set, and then return another value in the same row. In this case we'll search the company name column, and then return the name of the Sales person. So I'll navigate over to our Watch list, and I'll write =VLOOKUP.

The first value we must enter in the VLOOKUP, is our lookup_value, which in this case is simply Blammo Corp.

The next entry is the table_array, so we'll simply select full data set. Next is the col_index number, and here we must specify the Sales person column.

Our first column is the Company Name, and this is assigned by Excel to be col_index 1. This means that the Sales person index would be 8. So I'll press 8 and move to the next value. The final value is the range_lookup, and here you can specify an Approximate match, or an Exact match. I'll say FALSE for an Exact match, then close the bracket and press Enter, And as you can see the VLOOKUP calculates the Sales person for Blammo Corp. That was a little tricky, so I'm going to do it again, but this time for Klimpys. I'll write =VLOOKUP.

I'll select the lookup_value.

I'll then select the table_array.

I'll then write in the eighth column, which is for Sales person, and then I'll write in FALSE for Exact match, and close the bracket, And again this gives me the correct answer, which is this time Baines. As you can probably guess, the Sales person for the remaining companies can be calculated by anchoring the array in the formula. So I'll press F2 to go back into the formula, and then F4 to anchor the array.

I'll then Copy, and Paste in to the remaining cells, And now we have the Watch list complete for all of our companies. As you can see from this example, VLOOKUPs are a great way of isolating individual records using unique lookup values such as transaction numbers, invoice numbers and company names. However VLOOKUPs do take a bit of practice, so as an exercise try calculating the Payment Date for each company on the Watch list in this column.

As always I'll leave the answer in the after file below the video. The one big constraint of VLOOKUPs is that it requires the lookup_array to be on the left-hand side of the output. So in our earlier example, I couldn't have completed the lookup if the Sales person column was to the left of the Company Name column. To avoid this problem always make sure that any lookup columns are placed at the far left-hand side of the data set.