In the past I've shown you in Numbers how to use the Lookup functions to look up a number
in a row or column based on another row or column.
But what if the data is two dimensional.
Like, for instance, here we have a table that has prices.
We have stores going across, these are store names, and products going down, these are
product names.
You want to be able to look up a specific cell based on a store and a product.
So, for instance, if you want to find the price of an orange in the North store it'll
be 62 cents.
But how do we look that up in a table.
For instance here we have a table of sales and you can see the first sale is the West
store and it's an apple.
What's the price?
It should be able to look up here to find out that the West store apple price is $1.09.
So how can we do this using formulas?
We're going to look at how to do it using two functions.
The Match function and the Index function.
So we're going to learn how to use those in the process.
So I've got three blank columns here.
We're going to use that to experiment before we end up with our final formula.
It's important to know how to do this because sometimes formulas can be so complex just
building them all at once right out of your head is very difficult.
This is how you build complex formulas.
We're going to use a blank cell right here.
I'm going to hit the equals key and I'm going to lookup Match to just refresh my memory
of what Match looks like.
Match takes three parameters, search for, search where, and matching method.
Matching method isn't important here because we're looking for an exact match.
But search for and search where is what we want.
So we're going to type MATCH and the first parameter is going to be what we're searching
for.
So we'll click here on the name of the store on that row.
Hit comma.
Now where's it going to search.
Well it's going to search this entire row here, this header row, that has all the store
names in it.
I'm going to just click here on the left side and you can see it selects the entire thing.
It says it's going to search in the prices table and it's going to basically look in
the area here for it.
I'm going to close the parenthesis and hit Return.
I get a 3.
Indeed I can find the West column is the third one.
One, two, three.
Let's use MATCH again to find the product in this column here.
So I'll click on the column header there and now you can see it's going to correctly identify
apple as being in the second row.
First row, second row.
Great.
So now we need to learn about the Index Function.
So we'll do INDEX and we'll search for it here and we can see the parameters.
Basically it's going to take a range and then it wants the row and the column.
So the range in this case is going to be this entire table.
So we'll select this entire table here.
Now it wants which row in this range.
So the row is the vertical dimension there so it's the second one.
It's basically the product.
Then column and that should then match it up.
Sure enough there it is!
It gives me $1.09.
If I were to go and change now this to say, oh this was supposed to be the Center store
it will change to $1.04.
If I change it to it's supposed to be a banana you can see it's 77.
So it's always matching there.
Now we don't want to have all these extra columns here.
So we're going to want to do this in one formula.
So taking the knowledge that we've learned from here we can now go and say we want the
Index of this whole table and the first parameter here is going to be Match of the product here.
So it's going to be which row matches so we want to Match the product to column A and
then Match and then the store to the first header row there.
We've basically done the same thing but instead of three separate cells we have it in one
cell.
Now we get that there and we can delete these and we can, if we want, collapse this table.
Now I can Copy and Paste this where I want.
So you can see East, Peach matches, see there's East and there's Peach, 27 cents.
Valley, banana matches Valley banana 43 cents.
Now if we want to keep entering new data fortunately what happens is this formula gets repeated.
So as I enter a new one, you can see I get a little alert because it's missing data.
I'll enter in, let's say this is going to be the Airport store and this is going to
be an orange.
You can see the amount automatically gets filled in correctly.
Airport orange $1.10.
So that's how we can do two dimensional lookups using the Index
and Match functions.