0:12
Discount is calculated using two parameters however,
the item category discount and the customer category.
To get the discount amount, Alex is going to need to look these both up in
the discount matrix on the Discount Matrix tab.
This discount matrix shows the percentage discount for
each combination of discount category and customer category.
So for example, if your Discount Category is C, and
your Customer is Silver they will get 7.5%.
Now the VLOOKUP is ideally suited for looking up a single value.
But here we're trying to lookup two values, so VLOOKUP not the best choice.
We could try a combination VLOOKUP and HLOOKUP, but it's going to make for
a very clumsy and inefficient calculation.
So instead, we're going to look at a new function called the INDEX function.
This is another very powerful and versatile lookup function and
ideally suited to solving this kind of problem.
Let's see how it works.
So we're going to click on E11 and type =in and Tab to select INDEX.
The first argument that requires is the lookup array.
This can be a single column or a single row or an entire matrix.
In this case, we need the entire matrix, so I'm going to select that.
You'll notice Alex is actually called this discounts, so next time,
we'll use the name range and type comma.
1:42
The next argument the INDEX requires is the row number.
Now, if we have a look at our discount category C, it's the third row.
So we're just going to type a 3 and type a comma.
The third argument is optional.
If you only have a single column,
you don't need to specify that it's column one.
But we have four columns here, and Silver is in column two.
So we're going to type the 2, close our brackets and
we will click Enter, that's our 7.5%.
So in simplest that is how the INDEX works.
But what we did here was very contrived, by the time we've actually looked up
the row and looked up the column, we've done most of the hard work.
So really we need Excel to automate that process for us as well,
and to do that we're going to need to use the MATCH function.
And typically, you see INDEX and MATCH working together.
So what we're going to do now is replace this 3 that we had to lookup ourselves and
type in with a MATCH.
So I'm going to delete that, type m for MATCH and Tab.
Our lookup value this time is our discount category, so
I'm going to click on the C and type a comma.
And we're going to look that up in this little column over here of discount
categories.
Again, note this range has been named and type a comma and
it is an exact MATCH, so 0 and close our brackets.
So that's our first MATCH that we'll look up our row number.
Now, let's do exactly the same for our column number.
So remove the 2, select MATCH,
we're going to look up the value Silver in this little row over here.
And it's an exact MATCH close your brackets for your MATCH, so
that you end up with a black bracket at the end and click Enter.
And once again get the 7.5%, but now if we change our
Customer Category to Gold, it updates our Discount Value.
So that is the INDEX MATCH,
we're now going to apply exactly that logic to our customer quote.
So we're now going to click back into our Customer Quote and
calculate the Discount for each of these items using our INDEX MATCH.
While the Discount Category will vary for each of the items,
the Customer Category remains the same.
So rather than having to do that MATCH 60 times, we're just going to do it the once.
So start by clicking into I4 and type = m and Tab for MATCH.
Our lookup value is our Customer Category, so we're going to click on that.
4:24
And our lookup array is our Customer Categories, so
we're just going to type cu and choose Customer Categories.
And then of course make it an exact MATCH, close your brackets and
if you remember Gold was in column three, so when we click Enter.
There we go, column three.
We now need to do the rest of our INDEX MATCH.
So I'm going to click into H6 and
we're going to type = in for INDEX and Tab.
You will recall, our lookup array was called Discounts.
So I'm going to type disc and select Discounts and then type comma.
5:04
Now to get our correct row, we're going to need to look up our Discount Category.
So we're going to do a MATCH, and we're going to look up
the Siscount Category in our Discount Category list.
And again, that's an exact MATCH, so put your 0,
close your brackets and type another comma.
The column, we've already looked up that was our column for Gold.
So we're going to click into I4, and
then don't forget to press F4 to apply your dollars.
Close your brackets for your INDEX, and now when we click Enter,
we get our Discounts for each of those items.
Not only that, but if we were to upgrade our customer to Platinum,
you can see all the discounts update accordingly.
The index match is a little more complex than the VLOOKUP.
However, it does have certain advantages.
We've seen how we can look up both a row and a column simultaneously.
The other benefit is unlike the VLOOKUP where your lookup column
must sit to the left of the value you want to return.
The INDEX MATCH has no such restriction, so it's a little more versatile.
The INDEX MATCH also has the advantage that you can split out
the look up from the return.
In other words,
you can split the match from the INDEX where you can't do that with the VLOOKUP.
And so if you were trying to look up multiple values in the same row, for
example, by creating an INDEX MATCh and splitting out the match part.
You can create a far more efficient formula as well.
All these lookup functions take a little practice to get used to.
So don't forget to do this week's practice challenge, and
take some time to see how you could start applying these different LOOKUP functions
to solving your problems in the workplace.
[SOUND]
Dr Yvonne BreyerDirector, Learning and Teaching
