All right.
So talk about index.
We talked about match.
Now it's time to talk about how to combine them to do some really cool stuff.
In fact when you combine them they act almost like a lookup function but in some cases with even more
flexibility so they can find values in any column or any row within a given array.
So let's take a look an example.
It's a little tricky to wrap your head around at first but once you break it down into its individual
pieces or components you'll see that actually makes quite a bit of sense.
So what we're going to do is we're going to write an INDEX function but we're going to nest match functions
within it.
So in this case we're saying all right Excel let's find a specific price within this little price array
from B to 34.
So it's going to say index that array B to 34 but rather than just saying you know move to the third
row down and the second column over which is giving it very fixed directions I want to make it dynamic
.
I want the user to be able to input some value that will determine what cell we land on within that
array.
And that's exactly what the match functions are used for.
So if you recall the first piece of the index function is the array which is B to 34.
The second piece is the road number so how many rows down do you want to move.
In this case the number of rows down that we want to move depends on what product you care about sweater
jacket or pants.
So what we can do is create a match function that says okay we're going to let the user enter whatever
product they're looking for and sell B-6 and we're going to say it match that product within the range
from a two three four.
And then the answer's going to tell you how many rows down to index.
And then similarly allow the user to select what size they're interested in small medium or large.
And when you insert that into a match function based on the array from B 1:31 that will tell you how
many columns over to move.
So it looks kind of like a beast of a function but when you consider each individual match formula kind
of starting inside out and think about what the output of each of those match functions is you end up
with just a really simple index.
So in this case if you've selected pants and medium this is just going to say index B2 to D4 go down
to the third row and the second column and return the number 30.
Because a median pair of pants cost $30.
So let's jump into Excel and actually write one of these things from scratch.
Same exact concept here.
We've got a hypothetical price checker tool and this case we have five different size options and five
different product options.
And our goal is going to be to populate this cell here with the price and using an index function with
match components to pull out certain or specific price out of this array.
First thing we need to do is add some data validation because I want the user to be able to drop down
the options for products and the options for sizes rather than having to type it in.
So I'm just going to select B 10 going to data.
Data Validation allow a list and that list is just going to be the list of products that you're able
to choose for.
OK.
As you can see that's worked.
Same thing with C 10 data.
Data Validation allow a list and the source for that list is going to be all of the sizes that you can
choose.
In this case those live in C2 32.
So he's going to use that list.
OK.
And now I'm able to select any combination of products and sizes.
So that's step one step two is writing the Index Match function.
So we're going to start with equals index and what array are re indexing it's going to be the total
array of prices that we can choose from.
And since this formula is only going to live in this one cell I can either press forward a fix that
or I can leave it relative.
Doesn't really matter in this case but now I'm going to Khama over now excels looking for the row numbers
or how many rows down to move.
And again it doesn't make sense just tell excel you know row 1 to 3 or 4 because it's a function of
what product the user has selected.
So I'm going to insert a match function here and when I say match the value that the users put in Cell
be 10 and where am I going to look for it in the list of products from B 3 to be 7.
So that right now I have socks selected.
This will output the number one because it's first in that array it's in the first row within that array
.
And so the index function will stay on row 1 which is what we need not least match type I'm going to
do zero.
So I want an exact match here.
Can it close that off and that match function is done come over.
Now I'm in the column number component of my index formula.
So I'm going to just insert another match function.
Now we're going to look up what size the user cares about or what size the user has selected come over
to the lookup array which is going to be the list of sizes from C2 to 32.
And then again exact match for match type clothes that off to finish the match function.
And then one more parenthesis to close the index function and that should do it.
So I get four twenty five.
Right now I have small socks selected which looks right and I can just you know pop around different
values to make sure it's working.
Small pants 25.
There you go.
Extra large pants are twenty eight extra large T-shirt is 17.
There you go.
Index Match