INDEX MATCH Explained (An Alternative to VLOOKUP)

in this video I'm going to explain how to use index and match instead of vlookup [Music] so in this video you will learn why we would use index and match instead of vlookup I'll then explain the match function by itself I'll also explain the index function by itself then we'll bring it all together to write index and match formulas that can be used instead of vlookup and finally I'll explain the most common air you'll encounter with index match and how to fix it so we're first going to take a look at the benefits of using index and match into stead of vlookup and this excel file I'm using I'll make this available for free download I'll put a link to that in the description below this video and I highly recommend and encourage you to not just follow along but also practice these techniques so one of the first major benefits of index and match is that it can look to the left so vlookup we're kind of stuck only being able to do a lookup to the column to the right of the lookup value but with index and match we can actually look up and return a value in a column to the left of the lookup value and then another benefit is that we can specify a single column that contains the return values like you see in this diagram here like just specify one single column with vlookup we need to specify the entire table array and as you might know if you insert or delete columns that can break your vlookup and I have a few examples of this here so the first one on this vlookup breaks tab if we we have a vlookup simple vlookup formula here that we wrote in the first video if we were to insert a column here I'll just go to column C select it gonna hit ctrl + on the keyboard to insert a column or right-click insert you can see that my vlookup formula breaks and that's begin again because I'm specifying the third column here as the column index number and that column is now blank and then we can also we cannot use vlookup to look to the left so in this example here I want to look up this name in this column here and then return the region from column a column to the left and unfortunately we cannot use vlookup to do that but we can use index and match so we're first going to look at the match function now the match function is very similar to vlookup it performs the same basic lookup the only real difference is that vlookup is going to return a value from a cell and match will return a column or row number I have excels definition with the arguments here and then a simple definition just like I have for the vlookup with just what these arguments mean the what the where and the closest match so let's jump over to this match example sheet and we'll write out a match formula so we're going to start and sell C 14 I'm going to type equals and then start typing the word match and you'll see that is right here we can tab into that now again match just has three arguments instead of four like vlookup the first is the lookup value so this is the same as vlookup we're going to specify a value we could specify that with text in quotes or we could just select a cell that contains the text or the lookup value so we have that there then we're going to type a comma the next argument is the lookup array and again this will be similar to the vlookup with the table array except that we're just going to specify either one column or one single row so here I'm just going to select this this column that contains the items that we want to look up because again we're looking up this value in this column right here and so that's all we need for the lookup array of course we'll hit f4 to anchor it and still good practice to anchor or make this an absolute reference then we'll type a comma and the last argument is the match type so this again is similar to vlookup where we have an exact match or we have options for approximate matches here for less than or greater than but in this case we're going to use the exact match so we're going to specify a zero you can either select this and tab into it or you could just type a zero here for the last argument then we're going to close the parentheses and hit enter and we'll see that we have a four returned here so this is the row number where the match function has found cafe mocha it's looked in this list again I'll hit f2 here it's looking in this list or this column right here that we specified and it finds it in the fourth row of that column again this is not the fourth row of the sheet it's relative to the lookup array so it's the fourth row in the lookup or right here finds cafe mocha I'll hit enter and that's what we have returned right here so match on its own isn't too useful for us if we wanted to return a price we'll need to combine it with the index function in order to do that before we do that I'm going to jump to this match column sheet I just also wanted to explain that we can use match to return a column number as well so in this formula here if we edit this formula it's doing a similar process of finding this lookup value which is now grande in this range here in this row c3 to III let's just write up here and then it's going to return the column number within that range or within that row there again it's relative so if we hit enter now we're going to get a two that's returned here for grande because that's the second column in that range so we can use it to either return a row number or a column number again the important thing to note there is that within this lookup array we're only specifying a single row or a single column and then we can combine it with the index function to actually return a value from a cell so now let's take a look at the index function and the index function again is used to return a cell's value and this is going to be based on the intersection of the row and column numbers of a range so here's excels definition and then again I have this cheat sheet for you with just my simple definition of these three arguments for the index function so let's jump over to this index example sheet and we'll take a look at an example so in cell C 14 I'm going to start typing our formula we'll put equals and then start typing index and we can tab into this now when we tap into index you'll see we get two options here and we're really just going to worry about this first option which is has these three arguments for the array the row number and the column number we don't need to do anything at all to specify that we're using the first option Excel will automatically figure that out for us so it's just good to know that we're really focused on this version of index with these three functions I'm sorry these three arguments right here so the first thing we're going to do let's say we wanted to return a price for our cafe mocha from our table we're just going to specify the array which is the range of values where we want to return a single cells value from so we're going to specify this range right here again it's a good habit to anchor this or make it an absolute reference so this would kind of be similar to the table array in a vlookup these are all the prices all the possible values that we want to return and then for the next argument we have the row number so I'm going to type a comma and for our row number for right now we're just going to type it in manually and we want the fourth row again we want cafe mocha so down one two three four rows so we're going to type the four there for the fourth row comma and then our column number will be the second column because again first column four tall second column four grande so we'll type a two here and then we're going to close the parentheses for the index function and we'll hit enter and we can see that we get the price returned of 395 now when we're using the index function in place of vlookup we actually don't need to specify the column number so I'll do an example right here again equals index tab into that for this our array can just be a single column so if we just want to return the price of a grande site grande we can actually just reference this single column right here again f4 to anchor it then we'll type a comma and you'll notice that only the row number is a required argument here since the column number is in square brackets there that means it's optional and we don't have to specify it so here we can just type a four and then close the parentheses we only need that those two arguments and hit enter and we'll still get the same result of 395 so that's an example of the index function by itself again it's really just a container to point to a specific cell and return its value once we combined it with math that's when it becomes powerful and a powerful alternative to something like vlookup so now we'll take a look at how to combine index and match to use instead of vlookup I'm on the index match example sheet and here we're going to write a formula an index match formula to return the price of the cafe mochas sized Grande so in order to do this and I have a cheat sheet down here as well of how I typically write out index match formulas to make it a little easier because the formulas can get a little bit complex so we'll first start with our equal sign then we'll type index tab into that our array for this example is just going to be the column for the size grande we can just reference this column here again f4 to anchor that then we're going to type a comma and now for our row number we're going to use the match function so we'll start typing match tab into that our lookup value in this case is going to be our cafe mocha so we'll select cell b12 then we'll type a comma here and the lookup array is going to be the items here and column B so we'll just select those and then again f4 to make that an absolute reference comma then we're going to type a 0 for exact match and then we'll close the parentheses on our match function and again we only need these two arguments for the index function which are the row number which is our match function here and the array so we can now close the parentheses on our index function as well and go ahead and hit enter and you'll see we get the correct result of 395 so again if we just step in and look at this the match function is really doing the work of the lookup the vlookup here in place of the vlookup to return the row number for the item it's actually looking up the item here and column B and then the index function is saying okay now that we have that number number 4 we want to return the value from row 4 in this range here and column D so just going down to the fourth row there and returning that price of 395 so the major benefit again here between index match and vlookup if we go over to this sheet is that we can insert columns so if we just go right here for column D or select column D ctrl + to insert a column we can see that our index match function right here still works still returning the result of 395 because even as we insert columns we can insert as many columns as we want these range references will still stay the same and even though they're made absolute references as we insert columns between them the references will still change and they'll still remain so this will still stick to the grande column here if we were to delete columns the same thing would happen now that's not necessarily the case with our vlookup right here now with our vlookup we're specifying growth I'm sorry column 3 is the index number and that column is now blank within the table array so this is where index and match wins because it requires less maintenance we don't have to worry about maintaining the formula if we insert or delete columns within our source table up here we can absolutely do that an index and match will still work and the other benefit is that we can also use index match to look up to the left and that's what I have in this example here so for this formula again we have the match function that's looking up this name in column C and then it finds the matching row number and it's just returning that to the index function and in this case the array for the index function is column a so we're technically returning a value to the left of the lookup value we cannot do this with vlookup however we can do this with index match because there are two different ranges here that we're referencing the array and the index is one range and then the match where we're doing the lookup is in a separate range within the match function so instead of having to modify your spreadsheet to move columns around to do a vlookup you can just use this technique with index match instead and I also wanted to show that we can use a Dex match for both the row and column numbers so in this example here we have a similar formula but you can see we're using the match function twice we're first using it for the row number that's going to return the number of size cafe mocha from column B and then we're using the match function again for the column number and in this case we're looking up the size in row three right here and returning that column number so that's going to return a two to our index function and then for the index functions array range we're just referencing all of the cells within the price table here so all of these cells between these header rows and columns and so this looks like a long scary formula but when you break it down it's really just two lookups and then the arrange for the index that array and when we hit enter this is going to return that price I've also turned this into a bit of a price calculator by adding dropdowns for each of these items so we can choose the item here if you want also go over here and choose the size and then the index match function will of course automatically recalculate to show that price so this is another solution that can really make your formulas more flexible and dynamic for example we could add a column here just ctrl + again if we wanted to add a new size we could do that right here and fill it with prices and the index match function is still going to work it's just going to eventually find this new size here or whatever we specify it as and the prices and once we change our size us choice or selection over here it will automatically all work to return the correct price so I challenge you to do that go ahead and insert a few columns here make up some sizes in prices and just see how that will work with the calculation so let's now take a look at the most common air with index match and it's actually this ref air I'm on the most common air sheet and in cell c12 we have an index match formula and it's returning a ref air and this is because the ranges are different sizes so you can see our array range here only goes down to row 7 - cell d7 however the match function is going down to cell b8 and this value that we're looking up in column B is in fact in cell b8 so it's the bottom there so therefore the match function is going to return a 5 as our row number here this will return a 5 however we only have four rows specified in the array so therefore this is going to return a ref err this would be similar to a vlookup where you specify the column index number that's outside of the table array so this is a pretty simple example and it's pretty easy to spot this mismatch here where this tends to happen a lot in the real world is when we're working with larger data sets in this sales datasheet I'm trying to return the region using an index match formula here from this region sheet and this is a longer table over here and what happens sometimes is when you're typing out the formula or writing the formula and you're using keyboard shortcuts if you use ctrl shift down arrow here to select this range that's only going to go down to row 26 because there's a blank cell here so if you use that keyboard shortcut and you're selecting your ranges you might only get to row 26 or whatever this row is before the blank and that's not going to select the entire column of data and that's exactly what's happened here in this particular formula you can see this is only going to row 26 here however the match function the lookup array and the match function is going all the way down to row 52 so we have a mismatch here and that is what's causing our error so again we need to change this to row 52 now one good workaround for this to just prevent this error altogether is to use Excel tables and I have a column here where I have the same formula however I am using an Excel table for the lookup table and you can see here are the table references so this referencing table too and then the entire region column and same here we're referencing table two and the entire state code call it and this is on the region table sheet so those references will reference the entire columns regardless if there's blank cells or not it's always going to reference the entire column even if you add or delete rows will still reference the entire column and then that will help prevent that ref air in your index match formulas I have a whole nother video that explains Excel tables in more detail and I'll put a link to that in the description below this video but ref airs can definitely be frustrating so hopefully that helps you figure those out now you can also use the if air function here you can wrap index match in the if air function just like we saw in the last video with vlookup it's the same technique of wrapping it in if air and then out at the end you can do comma and whatever you want to put there for an air but again those same rules apply here you just don't want to wrap every single index match formula in F air first you first want to figure out what's causing the air if you're okay with those airs then wrap it in the if air to handle them so I hope you enjoyed that training on using index match as an alternative to vlookup we also have more in-depth training on the next match formulas in our elevate excel training program I should note that the new X look up function can also be used as an alternative to either vlookup or index match if you're on a version of Excel that supports it I have a separate video on X lookup and I'll put a link to that in the description below this video so I'd like to know what you plan to use index match for now that you know how to use it so please leave a comment below with your answer thanks again for watching have a great day and I'll see you in the next video [Applause]

Channel: Excel Campus - Jon

Views: 769,779

Rating: 4.9406605 out of 5

Keywords: excel, excel campus, excel formulas, excel index match tutorial, excel tips and tricks, functions, how index match works, index, index function, index match, index match in excel, lookup, match, match function, microsoft excel, ms excel, ms excel - index function, teachexcel, vlookup, index formula, excel formulas and functions, how to use index match

Id: yH_ArqoB0no

Length: 19min 28sec (1168 seconds)

Published: Thu Oct 03 2019