 Hello everyone and welcome back to another tutorial. In this video, we're going to be showing you how to properly use the index plus match function combination. The main reason or benefit for using index match is it gives us more flexibility than using VLOOKUP on its own. So the problem you have with VLOOKUP is you always need to make sure that your lookup value is in the far left hand column of your data set so that you can return only values that appear to the right of it. In our example, as you'll see, we want to return values that appear to the left and the right of our search or lookup value. So in our example, we've got some simple data on the left hand side here. We've got the region, the city and the revenue and profit for each of those cities and on the right hand side you can see we've got our city and this is going to be our lookup value. So at the moment we're set to Cambridge and we want to return the revenue and or the region and the revenue for that given city. So to get started, we'll quickly look at index to show you how that works. So index how it works is all we do well the easiest way is to sometimes just jump into it. So if you go index, open brackets and you can see there's quite a lot of arguments we can provide for this function, but only for starters only focus on that top row and we'll just jump through what each bit means. So the first part is the array and when we work with index, index is allowing us to actually do the return aspect of our desired function. So we are able to enter an array or an arrange of which contains a number of values and we can then provide a number for the row and a number for the column which will locate where our desired value is and it index will then return that value to us. So when we want to come to entering it in here, all we need to do if we want to return a single value is select the column it's the whole column and then we can go comma and let's enter the row number and we'll go for the number of two. So at the moment you can see we've got our array which is this column B and we want to return the row number two. So basically position number two from that range that we've provided. You can see that column number is surrounded with the boxed brackets and that means it's an optional function or an optional entry. So we don't need to provide that detail, but we'll come on to that in a second. So having populated as we've done, we can close brackets and enter and you can see we get the value of Great Britain return to us. And the reason we get that is because Great Britain is in the second row of our array that you can see highlighted here on the left-hand side. If I was to change to number two to the number of five then what will happen is our index will now return to us the value that appears in row number five of our array and you can see that is now North America. If we were to go back into our function as we mentioned and you can see we now got the column number. Let's put a column number having put the comma in there of the number two. So we're saying we want to look up in our array. We want to return the value that's in row number five and the value which is obviously in column number two. So this is kind of like giving our function the coordinates as you would for a map. So you've got the longitude and the latitude. So we want row number five and column number two. And when we hit enter on this, you can see by initially we'll get an error. And the reason for that is because at the moment in our array, we've only provided a single column of data. And when we want to then go into a second column, so we're trying to jump across here to city, it won't do it because we're moving out of our provided array. To get around that, all we need to do is just ensure that our array is extended out like so. So it now covers all the rows that we need and the two columns that we need. And when we hit enter, you can see that our value of New York is returned. And just to visualize that, what's happening is in our array, it's coming down to the fifth position. So we've got one, two, three, four, five, which is North America. And the region is the first column within our array and city is the second. So it's going to go to the second column and that's how we then get the value of New York returned. So that's how the index function is working for us. So the key facts to remember there is if you want just one column or you're only looking in one column, all you need to do is just select the array of a single column and the row number to return from it. If you want to return a value that could sit in multiple columns, then all you need to do is ensure that the column number that you provide is the same as the array that you've provided or that the array extends the number of columns from which you wish to return. So that's the first part and that's how we return a value. So as you can see, we need to input integers or numbers into this index function to better tell us to where to return information from. And in order to populate those numbers, because we don't want to be putting five and two in here, we want it to be done dynamically, but this is where we're going to use the match function. So just to clear that formula, let's look at how the match function works. So the first thing we'll do is to equals match. This is how we get into the match formula. And this works by the VLOOKUP aspect of what we're looking to do is it allows us to provide a value we wish to search for, an array in which to look for it and match will then return to us the position of that array the value sits. So we want to look for Cambridge. So we can select Cambridge from just above here. We'll go comma and then we're going to our lookup array section. So for us, it's going to be city. So we just need to select all of the values in the city for our lookup array and then do comma. And this time, we're going to be putting a zero in match type because that will make sure we get an exact match on our lookup value. So this is exactly the same as you would use in the VLOOKUP function. Obviously, with VLOOKUP, you'd commonly use the word false to get an exact match. But it's just doing exactly the same thing. Once we've done that, we can close our brackets, hit enter, and you can see we get the number three. So what that's telling us is our lookup value of Cambridge is in the third row or third position of our provided array. And we can see that is the case when we look at it over here. So match is now giving us that number we need to combine with index to return our desired value. So let's just clear this down and put in the two functions together, how they should work. So the first part we want to do is we want to do index and our index function starts by saying, right, we want to return a value from this column, so from the region. We now move on to the row number. So we now need to use match to say, what row from this range do we wish to return? So for us, we're going to do match, open brackets, we'll enter the lookup value of Cambridge and the lookup array for Cambridge is city. And the key point to remember at this point here is we need to ensure that our index array and our match array are the same in that they start and end in the same rows. Else if they're distorted, then the problem you're going to have is the values aren't going to coordinate with the right value. So we just need to make sure that these two ranges are corresponding in their start and end positions. So we've entered our match array or lookup array to a comma enter our zero to get the exact match. We'll do a close brackets to close or finish the match formula and one more to finish the index. Now when we enter, we can see we get the value of Great Britain return to us, which is obviously correct for example here. And if we now go and change this to say New York, we can see how our region is now populated or updated to show the correct region of North America. So this is how we're able to do use index match to return a value from the left of our lookup value, which again is something that we cannot do using the V lookup. So we now want to return the revenue. So this is going to be very much the same especially for the match part, but we're just going to be indexing data from a different row. So we'll enter this formula combination once again. So we'll enter index and this time our array is going to be the revenue column because we want to return revenue rather than region. Do comma. The row number is going to be exactly the same. It's going to be our match same match function. So we want to take the city and find out what row the city that we're looking for sits in. So we've set entered our lookup array there. Our match type is zero to get an exact match close to end the match and one more close to end index. And you can see we're now got the revenue of 98 or 9,805. And we can see for New York, if we go over here, we can see that's correct. So we've got the North America region, New York, and the revenue is 9,805. So that is how we can use again index match to get a different value this time from the right side of our lookup value. So to take this one step further before we finish and we're going to make it a bit more advanced is how can we also now have the ability to flick between revenue and profit? So as you see, we've got the revenue at the moment. But what if we wanted to bring profit in and have the ability to flick between the two as we so desire? The first thing we want to do is just quickly do a bit of data validation on revenue so that we can have that the drop down to have the select between the two. So we just needed to select a list, select our input values of revenue and profit and then go back. And now you can see we've now got our drop down available for us of revenue and profit. And at the moment, that's nothing's going to update our data here because our index is currently fixed on that revenue column. So we need to make a slight update to this front or this formula here, specifically the the index part. As you remember at the start, when we are working with or introducing you to index, we entered a second column and initially we got that error message. And we had to extend our index array to cover more than one column. And that's simply what we need to do here is we just need to make sure that this array of index of D6 to D7 is extended over the column E so that we can now source information that's stored in a separate column. So we make that change there. And then the next addition we do is we just get rid of one of these brackets in here. So we can now go into the column number by entering hitting comma. And to get the column number, all we now need to do is we need to do another match function to identify to us which column we should be extracting from. So revenue being the first and the profit being the second. So we just do equals match, open our brackets. And this time we're going to be looking at our drop down here. So we can see what value is because obviously if it's revenue, it's going to give us it's going to say it's position number one or column number one. And if it's profit, it will say it's number two. And it's that number that allow us to then jump between the revenue and the profit column. So having selected that, we then need to select our lookup array, which is those two values there. All the column headers, so we say. Another zero just to make sure we get an exact match. Let's close brackets on the match and close brackets on the index and hit enter. And at the moment, it looks like nothing's changed. But now with our drop down, we can select profit. And you see our former is going to update and say, okay, well actually we should be extracting from the second column for profit. And we're getting the value of three, nine, two, two. And just to visualize that, we can see New York here in row 10. We know it's in North America. The revenue is 9805. So let's go to revenue. Yet 9805. And then for profit, we're getting three, nine, two, two, which we can match here and confirm that the formula is correct. So this is a more advanced and even more dynamic way of using multiple match formulas with index to give a real dynamic lookup for us. So we hope you enjoyed that video. If you did, please do give the video a great big thumbs up. It's not only appreciated by us because we can then see the more content that you'd like to see more of, but it also really does help the channel in terms of the YouTube algorithm. So please do smash that like button if you haven't done already. And as always, if you haven't subscribed to the channel, please hit that subscribe button. Make sure you hit that bell notification button as well so you're notified of all of our future videos that come out onto the channel. So thank you very much for watching and we shall see you in the next video. Before we go, don't forget to check out the other videos on our channel. You'll see everything from other functions and formulas through to tips and tricks. We've also created some playlists so you can see these categorized together. So make sure you check those out and get all those useful information. And obviously as always, don't forget to subscribe and hit that bell notification button.