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.
- VLOOKUPs allow you to search for a record and return an associated value
- To work, the lookup value must be to the left of the value being returned
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
--- lookup_value: The value you want to search for
--- table_array: Array that has the lookup column as column 1 and the returned column
--- col_index_num: The column number from which you want to return a value
--- range_lookup: Always write false, unless you want an approximate match
CTRL + F: Search for a word or number
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select an additional cell
F2: Jump inside formula
F4: Anchor cells (when inside a formula)
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 dataset 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 LOOKUP 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 dataset, 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 salesperson. 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 salesperson column.
Our first column is the company name, and this is assigned by Excel to be col index 1. This means that the salesperson index will 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 calculation 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 8th column, which is for salesperson, 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 salesperson for the remaining companies can be calculated by anchoring the array in the formula. So I press F2 to go back into the formula, and then F4 to anchor the array.
And then copy and paste into the remaining cells.
And now we have the watch list complete for all of our salespeople. 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 salesperson 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 dataset.