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 a 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 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 have 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. ur first column is the company name, and this is assigned by Excel to be called index one. his means that the sales person index will be 8. So I'll press 8 and move to the next value. he 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 correct 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 8th 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 into the remaining cells. And now we have the watch list complete for all of our sales people. 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 to the far left hand side of the data set.