Before Alex can continue work on the customer quote,
there's one additional field he needs to correct on the price list.
So we're going to hop onto the Price List tab
and you'll see our Discount Category is missing.
Now the Discount Category is calculated per item based on the Retail Price.
So if the price is between $0 and $1,000, it's Category A,
$1,000-$10,000 is Category B, etcetera.
Now rather than going in manually entering all these categories,
Alex wants to automate the process of looking them up.
And a function ideally suited to this task is the VLOOKUP.
VLOOKUP is a very versatile powerful function and is used extensively in business.
The way the VLOOKUP works is, given a value,
like the Retail Price,
it will go and try and match it into a master dataset.
When it finds a match,
It will then return a corresponding value from the same row.
Now VLOOKUP is quite a strange name.
The V means vertical.
And the VLOOKUP can only be used on lookup data that is organized vertically.
If your lookup data is organized horizontally,
then you need to use the HLOOKUP function. It's identical
and because most of our data sets are actually vertical,
you're more often going to use and encounter, the VLOOKUP.
The VLOOKUP also has two slight variations —
we have a range lookup and an exact match.
The exact match is ideal for things like bank account numbers and
employee IDs where you want to find exactly that value.
But in this case, we don't have an exact match for most of these values.
So I don't have a 995.
Instead, I want to find it within the range $0 up to $1,000.
And that would return me an A.
So this is called a range lookup.
And that's what we're going to be looking at first.
One thing to be aware of with the range lookup,
is your lookup data must be organized smallest to largest.
Right. Let's see how it works.
So I'm going to click into E4.
In fact, I'm going to make this column a little bit wider so you can see what I'm doing.
And then, I'm going to click into E4 and type equals VL and press Tab.
Now with these more complex functions,
take advantage of these little prompts to
help you remember the sequence of the arguments.
And the first one the VLOOKUP wants is our lookup value.
That's the piece of information we know that we're going to use to lookup something else.
So that's my Retail Price and I'm going to click on that and type comma.
The next argument is the table or array.
Now that's our lookup data set.
And in this case,
it's this little block of data over here.
Now with the VLOOKUP, you select the entire lookup data block,
not just a column or row.
But don't include the headings as they're not part of the data.
Now before we go any further,
when we copy the data down we don't want our lookup range moving.
So it's very important that we immediately make it absolute by pressing F4.
If you come back and do this later,
you run the risk of only putting the dollars on the H7 or on the G4.
So please make sure you apply it to both sides of the range.
Right. Now another comma.
The third argument is our column or index number.
When you do a VLOOKUP,
it's always going to match the lookup value to the first column.
But, you then need to tell it which of
the other columns to retrieve the corresponding value from,
because in many datasets you may have more than two columns.
In this case, we've only got two,
so we're going to type a 2.
Note I'm not putting an H. The VLOOKUP has no awareness of the rest of the worksheet.
It just looks at this little dataset,
and so, it's the second column.
Now the fourth argument,
you will notice is in square brackets.
That means, in some cases it is optional and in the case of the range lookup,
it is completely optional.
We can ignore it altogether.
So I'm simply going to close my bracket and click Enter.
And as we would expect,
our first item is Category A.
If I come back onto that cell and double click my fill handle,
you can see it has correctly categorized all the rest of my items.
So the range look up is ideal for when you need to categorize data.
That might be looking up tax tables or grading or in this case, the discount category.
In the next video,
we're going to look at the slight variation which is the exact match VLOOKUP.
Make sure you're confident with this one though before you move on.
Dr Yvonne BreyerDirector, Learning and Teaching
