Sign in or start a free trial to avail of this feature.
1. Finding Records with VLOOKUP
VLOOKUP is a popular command for finding unique records within a dataset. Its awkward syntax does take some practice however.
To explore more Kubicle data literacy subjects, please refer to our full library.
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.
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.
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 a complete set of tools for manipulating and filtering data.
The sales -- that we're going to use in this course is for a software company with 150 customers, each who pay for their 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 will be to use the find function with the shortcut Control + 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 salesperson, which in this case is Parrish. However, if you have a long watch list this could get pretty tedious. 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 we'll navigate over to our watch list and write equals 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 the full data set. Next is the column index number, and here we must specify the salesperson column. Our first column is the company name and this is assigned by Excel to be called index one. This means that the sales person index will be eight, so we'll press eight and move on to the next value. The final value is the range lookup and here you can specify an approximate match or an exact match. We'll say false for an exact match. Then close the parentheses and press Enter.
As you can see the VLOOKUP calculates the sales person for Blammo Corp. This was a little tricky, so we're going to do it again but this time for Klimpys. We'll write equals VLOOKUP, select the lookup value, and select the table array.
We'll then write the eighth column, which is for sales person, write in false for exact match, and close parentheses.
Again, this gives us a correct answer, which this time is Baines. As you can probably guess, the salesperson for the remaining companies can be calculated by anchoring the array in the formula. So we'll press F2 to go back into the formula and then F4 to anchor the array.
We'll then copy and paste into the remaining cells.
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 the VLOOKUP formula is that it requires a lookup array to be on the left-hand side of the output. So in our earlier example we 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 to the far left hand side of the data set.