Using Index and Match Functions To Look Up Values in Numbers

You can use the MATCH function to get the location of a value in a row or column. You can also use the INDEX function to grab the value of a cell at a given location. This allows you to make 2-dimensional lookup tables to find values based on two variables. In this example, one table shows prices based on a store name and a product name. The other table will look up a price from the first table given the store and product.

You can also watch this video at YouTube (but with ads).

Video Transcript: 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.

Using Index and Match Functions To Look Up Values in Numbers

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information