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.