Practical Fundamentals of Index and Match in Excel and Google Sheets | Paula Guilfoyle | Skillshare

Practical Fundamentals of Index and Match in Excel and Google Sheets

Paula Guilfoyle, CPA, Online Educators, Lifelong Learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
2 Lessons (11m)
    • 1. Indexmatchintro

      0:30
    • 2. 2indexmatch

      10:27

About This Class

f7a049c6

Index and Match are both reference function in Excel and Google Sheets. If you combine both of these you will create a robust and dynamic two way lookup, to return values that you need.These are functions that should be mastered by every intermediate Excel or Google Sheets user.

In this class you will learn about the Index and Match, the syntax, how it works, and how to combine them in a formula to make a powerful lookup

Transcripts

1. Indexmatchintro: So you have mastered Look up in excel in Google cheats. But this is only a one way Look up. What? One variable? What if you have a table of data and you want to look up something from a particular row on a particular column? Well, to do this, you need to know Index and much Now both index and match a reference formulas, but combined, they make a very, very powerful look up. So if you want to be for pure spreadsheets and become more proficient, then jump right on into the class. 2. 2indexmatch: in this lesson. We're gonna look at the match functions and then we're gonna look at the index function, and then we're going to look at combining these functions together. Combining INDEX What match makes a very, very powerful, dynamic local former Let's first look at the match function on the match function will find a relative position in a list. So let's say you have a list of products on. You need to know where in the list is, say, fresh meat, while Excel will do is go down to the list until it finds the item that you're looking for . And then it's going to return the number two position in the list. Clearly, as this is a very short list, we could spot a fresh Meat is the third in the list, but imagine this list 100 or maybe even thousands of roles long. The match function requires a criteria or look up value on a list array to local black criteria in, and it will return the relative number position in the list of right that you have actually specified. Index looks up both the row on a column and what it does. This finds the crossover point. So let's say we have a table of data and you want to be able to select a product and a month and quickly returned the value off sales for that product on that one index will allow you to do this. The index function first requires you to select the table of data in which our local will be done. This is known as the Array. After this, you then need the road number and you also need the column number. So what the index function will actually do is go down to the specified world number and then go across to the specified number of columns on returned. That actually intersecting point between both un returned that particular value for you. So let's hop into excel now on Have a look at all of this by example. We look at Index, we look at Matt and we'll also look at combining these two functions together. Okay, here we are in excel again. Um, what we're going to look at burst is the much function, and you'll remember that the match function will return a well position in a list. So what do we mean by this? Well, we have a list here, which I've just highlighted, and we want to find the position that January falls in within this list. That's quite easy for us to identify that January is the second position in this list. But imagine that this list was enough locked longer and it would just return the position in the list. Let's look at this by example, and I have pulled in our drop down here, which I use data validation, which is seen in a previous tutorials. So let's put in the match function, so we always start with equals much. So the first thing it's looking for is the local value. We have to look up value over here now, if you were hard coating this in on again, I don't advise hard coding for good spreadsheet practice, but if you are hard coating this in because it's tech, you need to put it into inverted commas. Your look up Ray is next. So this is list in which you wish to find this particular local value. So here is our list here. Onda. We have a match type. We want an exact much so we'll select exact much and his injured, and we can see that May is coming in as the sixth position in the list. Let's look at that 12 tree 45 six. If we change that to January, it had come in as the second position in the list. Now let's have a look at it, for our products are coming down this way, so equals match. So first it's looking for our local value, which we have here and sell be 15 then looking for our table array. So we will select this whole list. Here are much type is an exact match. I'm well hit. Enter on. We can see the frozen vegetables is the seventh position in the list, so products is born to tree 45 take seven. So it's the seven position in the list. Now. You might think that this isn't very useful on its own, but in fact it is. It's extremely useful. It's extremely useful when you are looking to do a two way look up Andi. It's also useful when you combine it with other things such as Be look up and also when you combine a with his number and I'm going to show you is number. Now, what I have here is to list this one unless too. And I want to find out if the items endless too are also in list one. And I'm going to use the match function first to do that. So let's start equals much on my look up values. This is the value I want to look up. So I want to look up. This value might look up. Array is my table. Now I'm gonna fill this form of it down. So I'm gonna lock thes Sultan's well, press F four to luck in the table. And then I want an exact match hit. Enter and we can see that Apple's is there and it's number one in the list. Let's just fill that down. I'm with the oranges, is there? It's number two and bananas is there. It's number four in the list. Plums is and there are peaches, isn't there? Now this is a little bit ugly. So what we can do is we can add, is number to the start of this formula. And if it's a number, let me show you this name, So equals is number it injured? It's true because it has returned a number. Let's feel that down faults because the value that it gives, which is the position and the list is a number. So if it's a number, it's going to say true, if it's not a number, it's going to say fault. So we can very quickly use that number to give a true or a false result using the is number what we're gonna look and that is Index. And then we're gonna look a combined ing in combining index and much. What we're gonna look at now is the index function, and we said earlier that the index function will find the Intersect between two points between a row. ONDA COLUMN We have here January and we have frozen vegetables. So what will happen is with the index it to go across and it'll find January and it'll go down and it'll find frozen vegetables and it will return the intersecting point. Now watch. A need for the index function is you need the row number on the column number, which we have already actually located using the match function. So let me get straight into the formula. We can have a look at it in detail so that start with equals index on. The first thing that's looking for is theory, and that's the whole table of data in which you're doing your index and match. So let's select this whole table off data. The next thing it's looking for is the row number and the role number for frozen vegetables . We have already found to be seven, so we're gonna put in our cell reference where we've pulled in our row number, which is C five, and our column number is column number two. Because January is the second column in the table of data and we can close the bracket on hit enter and we'll see that it has found 1000 375. Let's change this. Let's go to July for frozen vegetables, and it's quickly pulled in 1488 Now what we have done is we have used match in a separate formula and added the result off match into index. But what you can actually do is you can actually combine index and match together into the one formula within the one cell instead of having to former this like this. So let me show you. That's begin with index on our array is the full table off data on the first thing it's doing is looking for your row number. Let's do match. Our local value is frozen vegetables. All right, look up. Array is rope is in column B from 4 to 10 on our match type is an exact match type, so that's our row number. But now we also need to find our column number. This much function here is the same as this much function here. So our role number we can put in match again on our look up value is July, which we have there in our data validation list, and we can select across the columns here we want. We want an exact match on. Then we can close the bracket on. We need to close the bracket again because we've closed bracket for much. We've closed this bracket for match, but we still have an open rocket for index so we can close that bracket and hit. Enter on. You'll see the index and match ball together has brought in the same value as index on its own, using match functions to feed in and give criteria for the index function. So that index on match, combined together and index and match is a very dynamic, versatile tool that you can use for looking stuff up. And you can see here when we change these that these figures automatically update So March for fresh meats is the cell here, one tree for two and a correctly pulls it in.