The ULTIMATE Index Match Tutorial (5 Real-World Examples)

welcome to the ultimate index match tutorial where we'll go over four examples from easy to hard and stick around until the end for one final bonus feature so let's get into it first up in level one we've got a simple index match with one variable so let's take a look at the example over here which you can download for free in the video description so you can see here that we've got a table with the countries and their respective sales and over here we want to use an index match to find out the sales for

Portugal so for this we'll go equals index is the first part of this function hit the top key there and the array here is whatever we're interested in so right now we're interested in getting sales so all we need to do is highlight the whole sales area so just select the first one and hit Ctrl shift down arrow to get all the way to the bottom of the data set then we'll hit the comma key now we need to match it right we want it for Portugal so we're gonna do the match function hit the

top key there and the lookup value is we're looking for Portugal right now so this one right up over here comma and we're looking for it within a certain range that's the whole range of countries over here so we'll select the first one and again Ctrl shift down to get all the way to the bottom there we'll hit the comma key again and for the match type we want to have an exact match so we just type a zero in there then we'll close the parenthesis that's closed for the match and now we need to

close it again for the index and hit enter from here if we look at the result you'll see that it says 65 000 which is exactly this number over here we can also change this to say Spain and you'll notice that it updates to this other figure so it's all looking good now moving up to level 2 and now we have multiple criteria so over here you can see that we don't just have the country anymore but we also have data for the month so you can see instead of just having January we have all

of these other months and so that's why we have two criteria now so for this we'll start in the same way equals index hit the top key there and the array for us is what we're interested in we're interested in sales so we'll select all of the sales data so Ctrl shift down and Ctrl shift right to select all of the sales figures hit the comma key there and now we gotta start with the first match so we'll put the match function hit the top key lookup value firstly we're interested in the countries comma and

secondly this is going to be where we can find the countries for the lookup array so Ctrl shift down comma and we want to have an exact match so we'll put the zero in there and close up parenthesis hit the comma key again and now you'll see that we don't just have the row number but we also have the column and that's where the second variable comes in so in our case it's for the specific month of January so we're actually going to use the match function again hit the top key there and the lookup

value for us is going to be hey we're looking for January comma and we're looking for January within this range of months so from this one we'll select it and then Ctrl shift right hit the comma key there and we want an exact match so we'll put a zero in there close up parenthesis and now we need to close it again for this index part so close and just hit enter now you can see that we have the sales in the month of January for Portugal which is this figure right here and it seems to

be matching all right now moving on to level three where we have a dynamic index match so here's a scenario you can see that we've got an income statement and we want to find out these specific lines so the revenue in 2020 gross profit net income and same thing for 2022 so in the previous examples we just had the answer in one cell but now we need to move it across all these cells so that formula is going to have to be fixed in certain areas while it's going to have to be dynamic in others

that's when we'll be using relative referencing so let's take a look we'll type equals index hit the top key there and we're interested in all of the figures so let's select them this is the first one Ctrl shift down all the way to the bottom and all the way to the right as well and now we need to lock these so we're going to press the F4 key and you'll notice that we get dollar signs in front of them this means that when we move them around they're always going to stay fixed in this area

and not move right or down we'll hit the comma key there and now we need the first match hit the top key and it's gonna be the revenue but the revenue we needed to move down we just don't want it to move across or it's going to go inside these figures so we can press the F4 key not once not twice but three times such that it stays fixed on the column but not on the rows will hit the comma there and the local parade is we should find it within this area here so we'll

go all the way down to net income hit the F4 key to lock that we just need to hit the F4 key once here as we want this to stay fixed we'll hit the comma we want an exact match close up parenthesis comma again and now we go for the second part which is the column so we'll do a second match lookup value this time we're looking for the years so this is the first one and we want this to move horizontally just not vertically so we'll hit the F4 key twice on this occasion such

that it stays within this row but it does move between columns hit the comma key lookup array is all of these years here so Ctrl shift right and we'll hit the F4 key as we want that to be fixed comma zero for an exact match close apprentices close it again and we'll hit enter from here we can drag this down so shift down arrow shift right arrow Ctrl D for down and control R for right now to test if it's all okay we're just going to select the last one and you'll see that it's moved

dynamically to net income in 2022 while the first one was at Revenue in 2020 so it's all moving well and if you're enjoying this tutorial and you want to level up your Excel skills you can consider checking out our Excel for business and finance course and what makes this course different is that it's all applied to the real world while we still cover theoretical lessons like formatting formulas and charts we also offer case studies that simulate the type of work you might be assigned in your day-to-day ranging from Financial modeling to cleaning a data set

and presenting some visual insights we also offer several other courses including power bi VBA Finance evaluation and more so if you're interested in checking it out head over to the link in the description below alright back to the video next up in level 4 we've got an advanced index match so here's the scenario for it and you can see that right now we have the countries like we did before with the sales figures but we now have January duplicated three times because they're split by years same thing goes with the other months and so we

actually have three different criteria one being the country the year and also the month so so far we've only looked at two criteria so let's see how we can work on a third one so first we'll go equals index then for the array here it's going to be all of the sales triggers that we're interested in so Ctrl shift down Ctrl shift right comma and then we need to do the first match and this one straightforward it's simply the countries which we've done before so we're looking for Spain comma and we're looking for Spain within

this area here so Ctrl shift down comma we want an exact match so we'll put a zero in there and close that parenthesis hit the comma key there to move into the columns and this is where it gets a bit more tricky because we now have two variables left so what we'll do is put the match function and now under the lookup value we need to add two lookup values we want to look for both the year and the month so we'll first put in the year and then we're gonna use the Ampersand to put

in the January as well in there hit the comma key and same thing goes for the arrays we have two arrays so the first one is going to be the year one so we'll go over here Ctrl shift right then we'll put the Ampersand and we'll add the second one being the months so Ctrl shift right there so again we've got Ampersand both for the lookup value because we need two and for the arrays because we need two as well comma we want an exact match close the parenthesis and close it again and hit enter

there so Spain January 2021 this figure here seems correct if we search this over to say Italy it seems to move dynamically and it's all correct as well now moving on to the bonus section and the main limitation with an index match is if there's multiple values with the same name for example if you had Spain more than once it wouldn't be able to aggregate those together and sum them so let's take a look at an example with a possible solution so you can see over here we have sales people and the thing is here

that we have Steven multiple times so we have it here once and a few more times down below and that's why an index match doesn't make sense if you want to sum all of them so the sales for Steven in the month of January for example so here we're going to have to use some different functions and the first one that comes to mind is using a filter so we'll go equals filter hit the top key there and the array well we're interested in sales so we'll select all of the sales figures so Ctrl shift

down Ctrl shift right comma and then what we want to include well we want to include Stephen so we're going to put all of the sales people here Ctrl shift down and we want those to equal to Steven we'll close our parenthesis and hit enter and so you can see that we now have a ton of data and that's basically all of Steven's rows so this one up over here is this very first row of data for him and so we need to somehow filter it down and that's because we don't have the second criteria

which is that we want it in the month of January so we need to add a second filter on top of this so at the very beginning here we're gonna put the filter formula again hit the top key the array is this whole area that we have so we're just going to go to the end hit up comma key now we need to apply the second filter which is that we want it within the months so Ctrl shift right to select all of the months and we want those to equal to the month of January

we'll close up parenthesis and hit enter and so now we only have Steven's data for the month of January but from here it obviously doesn't look too great that's because we need to sum these three so at the beginning of this filter function we're going to use the sum function hit the top key there and then we don't need to put anything we just need to close the parenthesis at the end and hit enter that's basically summed all of Steven's figures in the month of January and again this is all Dynamic so I can change

this to Billy and it's going to update to belly over here in the month of January so he's duplicated as well for more on Excel formulas check out this video over here to learn all about lookup functions or check out this link over here to take our Excel course hit the like And subscribe and I'll catch you in the next one