Hi, this is Wayne again with a topic “Excel: INDEX and MATCH”.
In this article, i will show you how to use two functions index and match to do powerful, lookups in microsoft excel and here’s the sample spreadsheet we’re going to use for this video. If you want to download it and follow along, you can find it in the description below this video, and what we have here is a record of sales for a hypothetical media business that sells these things and they’ve been tracking each month. How much they’ve sold in each product category? So what i’d like to be able to do is go over here on the right next to product category. I’D like to be able to type in – let’s say, dvds and the month of let’s say june and then be able to see the total sales of dvds for the month of june. So how can we accomplish this well using index and match is one of the best ways to accomplish that, let’s start by trying to understand each of the functions index and also match. So what index is meant to do is to help you pull out information from a range.
So, for example, i could use index to search this range and produce the information that’s in the second column and the third row, for example. So, second column third row. It should be 105 and then it would produce that result.
Let’S look at how it works here in cell j7, i’m going to type equals index and then left parenthesis as usual. Excel gives us some hints and some tips there’s a couple of different paths. We could take i’m going to stick with the top example. It’S looking for an array which is basically a range so just like.
In my earlier example, i’m going to click and drag to highlight the range of numbers in the spreadsheet. Next excel is expecting a comma and then the row number comma column number so i’ll put in my comma row, number comes first. So let’s say i do want to produce that 105. If you remember my selection started here and continued all the way down to here.
So this is row one, even though it says two here at the left. This is 2 and this is 3., so i’m going to type in the number 3 to indicate the third row and then i’ll put my comma. What column is this? Well, it’s the second column of what i had selected, so i type 2 and then i should put in a right parenthesis. In many cases you don’t have to put in the right parenthesis and then just tap enter notice that it produced the correct number 105.
So it worked and that’s what index does for us. It helps us to identify the third row and second column or fifth row and tenth column and then produces the results, whatever numbers we put into here, that will determine which row and which column it looks at and then produces the number or the data. That’S in that cell now, if you’re looking at that and saying to yourself well that has limited use. You might have a point.
If that’s all it did, i wouldn’t be too excited about using index. There are many other things you can use it for, but in a minute i’ll show you how you can pair it with match to do an index and match, and that really makes it very useful. Let’S first, though, move on to match here in cell j8, i’ll type equals i’ll type in match.
For my function, followed by a left parenthesis next excel is looking for a lookup value; in other words, what is excel gon na look for as a result of this formula. Well, maybe i want it to look for a specific month, let’s say july now, because july is a word and not a number, i should put that in quotation marks, if i want it to work so july, next excel is expecting a comma and then the lookup Array so i’ll put in the comma. What is the lookup array? Well, what that means is what is the range in excel that i want excel to examine and search for this word july in well.
The array is here i’ll click and drag to select each month. Next excel is expecting a comma and then look what it does. It’S saying, what kind of a match are we looking for? Are we looking for a match that is less than this, an exact match or greater than this? Now, when it comes to words, it’s kind of confusing to think about less than greater than but let’s go with exact match so i’ll just put in a zero that represents exact match. I should put in my right parenthesis and then just tap enter and it produces the number seven now. Why did it produce the number seven? Well because i was having excel search, this range, starting in a2 through a12, looking for the word july, if you count it out, it’s one, two, three, four: five: six: seven, it’s the seventh item in the array or range so that worked. Let’S do a second example. Let’S say i’m looking for the word software i could type equals match left parenthesis. My lookup value is software comma. The lookup array is this information here, comma and again i want an exact match so 0 and then the right parenthesis tap enter. Let’S check it to make sure it was accurate, we’re looking for software.
Software is one two three four five. It produced the right number again, if you’re thinking to yourself, that’s nice, but this isn’t very helpful okay, but what, if, instead of typing the word software here? What if i tied the match, function to something that changes, for example, dvds and june? This is an area of the spreadsheet that i expect to change. I expect to type in different months different categories, and so, if i can tie these match examples to something that changes it becomes a little more powerful. Let’S take a look at how to do that. So here in my match, example number one: i could delete the word july and instead of july, i’m just going to click in this blank month. Cell tap enter and it gives me an error – it’s not applicable. But what if i now put in the word july, tap enter. It produces the same result as before the number seven, but the nice thing about this is this is adjustable, so i could put in august and it changes i could put in january and it changes same with match.
Example, number two instead of typing the word software in quotation marks: why not simply click here where the product category will be typed tap enter on the keyboard? Yes, i get an error the first time when it’s blank, but now, when i type in the word books, it produces a result, and this is an adjustable result. I can change it to dvds and the match updates. Okay, so that makes it a little more useful and exciting.
These formulas now are tied to something that is a variable it changes now we can put both of these functions together index and match in one formula to automatically produce the sales of dvds in the month of january, for example. So, let’s do that i’ll click here in cell j5 type equals index left parenthesis. If you remember the point of index is to look at an array or a range and then produce results based on the column and row that are specified. So it’s first looking for an array: let’s give it that same array that we did before just click and drag to select that range now excel is expecting a comma and then the row number comma column number so i’ll put in my comma now.
What row number do i put here? Well, i don’t know which row number to put it’s going to be a variable, it’s going to change, based on what has been typed into this cell here for month, right that will determine the row and i don’t know, what’s going to be typed in that cell. So this is where we use the match function. This is using one function within a formula that starts with a different function index after match. We put a left parenthesis, it’s looking for a lookup value. I don’t know what the lookup value is.
It’S something that changes, but it’s going to be a month. So i’ll just click here. This is where the month will be typed by the end user notice. When i clicked there, it didn’t put the word january here. It put the cell reference j4. That’S perfect! That’S what i want comma lookup array.
What range is excel gon na search looking for whatever is typed in this cell. Well, it’s this range here, so i click and drag to select only the months no empty cells above or below. Just the months i put in my comma. What kind of a match is this going to be it’s going to be an exact match so 0 and then my right parenthesis now excel takes me back and reminds me that, yes, i’ve been using match, but this is really an index formula. That’S the function that started off this formula, and so it’s reminding me i need to now go in and put in a comma and then the column number, so here’s my comma for the column number again. I need to match it.
I don’t know exactly what the column number is going to be, but it’s going to be based on what’s typed here in the product category box that will determine which column i’m interested in left parenthesis now excel is looking for a lookup value. I don’t know what that’s going to be: it’s a variable, it will change, but it will be located here. So i click there, j3 comma. What’S the lookup array, what will be searched in order to find dvds or books or cds? Well, it’s this! It’S this range here, comma. What kind of a match are we looking for an exact match, so i put 0 and then right parenthesis and then i really should put another right parenthesis to finish off this formula. Tap enter and excel produces a result for me, 288. Let’S check it out, dvds, in january dvds, in january 288., now we can go in and make changes to these cells. Let’S say i’m interested in software in the month of october 30, software october 30., so this is working flawlessly now.
This is helpful and kind of fun to use with such a small spreadsheet, but imagine instead of just 13 rows and 7 columns. What if this spreadsheet had so much data that it had 50 columns and 10 000 rows, or something like that, this concept of combining index and match to pull out a specific result out of your spreadsheet? That would be even more helpful and useful. Now some of you probably know that you can do similar things with vlookup and if you haven’t already, you should definitely watch my video on vlookup thanks for watching. I hope you found this tutorial to be helpful.
If you did please like follow and subscribe and when you do subscribe, click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, you can do that through. My patreon account look in the description below for a link and also below the video you’ll, see my merch shelf. Please consider buying a t-shirt or some other product to show your commitment to technology for teachers and students.
.