The Incredible INDEX Function - Is it Excels Most Useful Function? | Alan Murray | Skillshare

The Incredible INDEX Function - Is it Excels Most Useful Function?

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
7 Lessons (49m)
    • 1. Introduction

    • 2. Example 1 - Using INDEX and MATCH for an Advanced Lookup

    • 3. Example 2 - Create a Picture Lookup

    • 4. Example 3 - Return the Value from the Last Row

    • 5. Example 4 - Create a Dynamic Named Range

    • 6. Example 5 - Sum the Last 6 Values Only

    • 7. Example 6 - Retrieve a Range from a List of Ranges


About This Class


In this class we show six examples of the incredible INDEX function of Excel. This is one of, and viewed by many, as the best function of Excel. This class will show you why that is.

We start by demonstrating the INDEX function with MATCH. This is the classic use of the function and how most Excel users hear of it. It creates a very flexible lookup formula.

We continue to see examples of it being used for a picture lookup, and also to create dynamic formulas to retrieve the last cells value or calculate the size of a range.

Using INDEX to create dynamic references is fantastic. It can be used with other formulas and also charts for dynamic charting.

So if you want to know why INDEX is so awesome.

Enrol and you will find out.

All exercise files are provided.


1. Introduction: Hello and welcome to this class on the incredible index function. This is possibly excels. Best function in a lot of people's eyes on. We're going to have a look at six different examples in this class. Off wire people rate it so highly. So this is what we're going to speak about, where you're going to begin by using index in its classic form and the format that you've possibly heard it for. On that is when it's combined with the match function to create flexible lookups. It is commonly found as an alternative to V look up, a superior alternative in many people's eyes. Always an easy way to start an argument of their vehicle, Cup of versus index match. But it is definitely more flexible. Whether that makes it better, is it sometimes debatable? But he can do things that vehicle cannot do, and it can also do them faster. But it is, too functions that's not index alone, that is, with the match function. Once we've done that, we're going to look at using it in quite Aneesh y, which is to create a picture. Look up. We want to change the value in a cell on on that changing value. It will produce a picture because Index is going to help us fetch that. On doing that, we will look at using it to return the last sort of value from the last road. So we've gone, ever expand in column, a list of values, and we always want the current one, which is the last one on. We're going to get index to do that. So in a typical business scenario, that could be the current month for the current week, which, as time changes, is always going to be changing. But Index will do that for us. Keeping with the dynamic nature will be using it to create dynamic named arranges. Now this is a brilliant skill to have because we can use these name ranges in our charts or in other formulas, our or conditional formatting ranges. So to create a dynamic nature as the size of that list may change or move or grow, Oh shrink or whatever happens, we can have our charts and their formulas react to it, which is fantastic. We could also use it to some the last six values, so kind of keeping in the theme of the last two bullet points there, showing different techniques for it. So we're speaking about dynamic ranges than getting the last value. Or how about the last six values? We're going to use it to some them here. But once again, that skill can be applied to things like charts and conditional formatting. Also. And then we're going to see one example indexing Qala a rare way. So this really is a kind of clever tipper guests, which is to see it return a range from a list of ranges. So this could be handy when used with things like Look up, values on, look at formulas even where we're going to return the look up range, if you will. But from a list of possible range is that the user can specify with a cell entry. So lots of good stuff in this class. But it's the index function, so it's gonna be good stuff because that itself is good to know. So, without further ado, let's get on with it, enrolled in the class and let's start seeing some top tips on Dwyer. Index is so amazing 2. Example 1 - Using INDEX and MATCH for an Advanced Lookup: hello and welcome to this lesson where we have the first example off the index function Off Excel, a function that is regarded by many Excel gurus out there as the best function of excel. A very controversial statement. But it is pretty good. There's gotta be up there somewhere. It is not the best. It pretty close. Now we're going to start this first example. We've possibly the most common situation that you will come across the index function and that is using it with the match function, as those two functions together can create a very versatile and very dynamic look up function. And a lot of people find this as a progression from veal cup. Villa Cup is amazing function, but it does have its limitations, which a combination of index and the match functions can overcome on. In this example, we're going to see two reasons why that may be so. What I've got here is have got this is tiny table with course I d on. Our mission is to return the company and the number of delegates from that course now over on a sheet. I've got called courses in this workbook we have the course, I'd be in column C. Then I've got a company over here in B and a number of delicates and a now for this. First off, the two examples of this video, I won't return the course. So the company from that course I d. Now in his current spreadsheet layout, that is something that Villa Cup cannot do. We look up, cannot return data to the left of where it's looking. So already, we're looking at something that can be achieved by standard. We look up alone, I'm going to see the versatility off index a match. So on this shapes let me click inside, so see free and will begin with our index function. And, as you can see here, for those of you who may never abuses before, the job of index is to return a value just like the look up or a reference that something extra from a cell at the intersection of a Roman column. Now the Intersection Row column stuff we are going to get match in this example of examples to come to do that for it. But important things that index can create references, good examples of that to to come and return values as very common dog today now have a double click on index function. To open it up, it promises for the array, the row number and a column number. I've got example of the second option there to come in this course as well, just looking at the 1st 1 for now. First thing that promises for the array to return the value from. So we're going to click on the courses sheet and tell it to return it from the company. The area. Now I could select an entire table here, but for this first demonstration, I'm going to select a specific column that I know it's in. And that's column B for me right now. Let me type in my comma, but it'll jump back to the other sheet just said that you guys can see what I'm typing a little bit easier. That's why I'm in a moment. Column B, of course, is is the array. It then wants to grow number from May. Now, that's to be I don't know what road is that course id'ing 4 to 0 to, and this is where the match function will be inserted. The purpose off match returns, the position often item in a range, and that is what Index wanting wants to know. What rabbits in match could do that for it. The combination off them, Matt returning positions. Industry turning values from my position makes it a very neat combo. Let me finish off my match function and open up the bracket, and it looks extremely similar to the U Cup. The first thing it wants is to look up value. That's the course I d. That really need that sheet reference in there. That's because I've been clicking between the sheets. It may not do it when they're when you run through this example, but let's be free. Basically here. Coma. The look up. A rape that's over on the courses sheet problem. See, look down Column C for the I d coma. Maybe just jump back to that sheet against you can see what I'm doing on the match type is going to be zero now, I noted. It problems you for three options for a match type as well. You may know the video cup prevent presents you with us till options true or false, you're a little bit more flexibility here as well. I'm simply gonna choose zero, though I'm looking for a specific I d. So it is a exact match that I want or closed bracket for the match function. That brings me back to Index, where I do have an optional argument for a column number as well. Now that's the column number to return the data from now. I don't need that because the original array of gave it is only one column. If that was a big table, Anita said, it will column it in but given it one column so that makes this redundant. I just need one more close bracket for Index. And if I press enter, that returns to company from that, I. D. 4 to 02 is seven seas imports, apparently at 4 to 0 to Was that do at seven seas imports? So for this next look up function, we're going to see it being used to return the number of delegates from this table and that we're going to see index unmatched being used for a two way Look up. Now, this two way look up example is going to demonstrate the true flexibility off the index and match function combination. We're going to use its return. A number of delegates. Let me start typing my index function in here. And when it prompts me for the array this time I'm going to the courses sheet and I'm harlot in the entire table. And let me just put in my comma and return back to the sheet that, like, have been doing. It's easier for you to follow. So the index prompts me for the row number. You don't have to keep coming back to the sheet dark. I am now. The row number is going to be a match function, just like a moment ago. The look up, the value is going to be the course I d. Let me get rid of all that sheet malarkey. The look up array is going to be on to courses in column C, and it's going to be a exact match, and I'll put a comma there. So I wrote quite a bit that time that we get rid of that. So I've got the match function looking for that I d. In column see exact match of Napoli McComas and now it's prompts me for a column number something I didn't worry about with the previous demonstration. So now I need to find what column number of delegates is in now. Very important here that the word in used in cell D to their the number of delegates header is an exact match for the word in in the head. A row off the table because I am going to match those up toe, identify the column number. So that is very, very what it's essential really Here. I'm going to use another match function. The look up value is the header so d to here the array is going to be the row off the headers on the court of sheet. You wanna cross to anyone or simply row one. It was a very big table. It will be more precise this time. Comma zero for exact match. Close off that bracket. That's his question. Makes having put that second bracket in. As I come over here, it tells me is going to do it for May. I think I'll say yes and then just open up that formula. So this time I'm looking at the entire table eight A. If you can find the idea in the road from the road number, and then the number of delegates head up in the header row and return a value for that intersection. Some matches going to run in this example and tell us that it's in road to 256 Match is going to run and tell it that it's in column five and Index simply returns to value from column five, row 2 to 56 Very simple job for it from that point. But that is why these two functions work so well together that it could look left and right , which really cut. Can't do that. A column number is flexible. Where's Villa Cup prompts you for an index number, specific number and intestine. That's done. If you work with large spreadsheets, Index and Match actually performs faster, then video carp in some of the other look up functions as well. So all in all, this is a more powerful, flexible formula. Doesn't necessarily mean that you need it. Now. If you're happy with, have a look up functions that use and it's pretty, it's on. That big may not be necessary, but he's Gemini, considered the ultimate look up for when you are returning the values from specific road and columns 3. Example 2 - Create a Picture Lookup: Hello and welcome to this lesson. The second example off the brilliant index function on. We've got another look up example for you this time with the index and match functions that this time we're going to do a picture. Look up. So I have a sheet called customers. And in here I have four customers and you may recognize some of their names, and I've got a lovely picture over them as they posed it very nicely for what I would like . My picture look up sheet is to be able to select one of my customers from my little data validation list hit and when I do so for it to return the picture in the cell below. So I've got a drop down list here. This picture is simply Justin Inserted picture. There's nothing special going on in there. A tal is just a picture, but what we're going to need to do to achieve this is used the index and match function, as I mentioned. But I'm going to need it in a named range ah contest. Type this into a cell has to go into a named range, and then I can link my picture to do that named range, and that will get it working. So let's see this in action. Now. I'm going to type the formula into a cell to start with so that you can see my working. Then I'm going to copy and paste it into the name of the range window. You're going to struggle to see if I type it straight into their. So it's going to be an index of match. It's very similar to the previous example, so I might go for a decent pace. Index function prompts me for the awry. Now that is what it's returning from. So if I click on my customers sheet on, highlight the array of pictures now I'm going to fix these references, as I got as well. So if I put my comma and just jumped back to this sheet, you can see I've used dollars to fix that range up. No, it now prompts me for the row number. That's what we need. Match to Dio. Let me type in match bracket. Look up. Value is the name of the customer that I have chosen in my drop dentist. Let me make the absolute and put a common. They look up awry over to customers. The array of names look for that customer name in that list of names is going to be an exact match, and I'm going to close off both rockets. So this is my end result as a sigh. It's similar to the previous example. Apologies for the speed, if that was too quick. But my formulas on screen now to discuss it a little bit more detail in the previous example. Previous video. But I am returning from this range. Pictures. If you can match the customer name to the list off customer names. Exact match. Now that's not doing anything here. As I say, I need this in the named range on. I need to leave my picture to that named range. I haven't done that yet, so this is pretty useless. But I'm now going to do, though is highlights that formula and take a copy of it. Let me escape that so and click on my formulas tab on my define name. But, um, I need to give my name. Hit my name, my name. I'm going to call it picked Search. This tried out typing again advocacy. The windows quite small. That's why I don't want to type it into here. But now I'm going to highlight this. Refers to area on do control V to paste in the formula. You just saw me, right? Okay, Okay to confirm that. And the named range is created. The last remaining step here is to click on my picture, click inside the formula bar and type equals. And then the name that I gave my range. It's pick search, Larry. Double click on that. So that's the link to the name range. That's where my formula is. I press enter to confirm. So now if I go to my customer, drop down and choose a customer such a group or customer, such a pepper pig. I now have this picture look up. It's looking for that name and return in the picture, associate it with them from the other shit. Now to take this one step over. What I've done on this customer shaped is of incessantly blank image at the bottom, so completely empty image there. Now, a card. Incorporate. This might look up formula or I certainly haven't been able to, but I'd like it to return empty picture kind of blank frame if there is no name in that cell. So when I got home of their or Pepper pig there and great, bring that picture. But if there's no customer, don't bring a picture. So what I'm gonna do? If I revisit my formula, I'm going to happen. If function around it, Just ask that if the sell contended customer name, he's empty. It was blank. Then coma. Return the picture for net cell. That's, you know, the blank picture. They're putting my comma. Otherwise, do the index match function. See if we look at my for me that now quite large. But I just got an it function saying, If that customer that said his empty return a blank image otherwise don't barber. If I take a copy that formula and go back into my name manager, step my pick, search and edit it. Or indeed, if I just pasted on the bottom here that should work. Let me meditate. I like doing in this window. Let me I can resize this, by the way, no need to right now, but if you find it easier, I can highlight that list. Let me delete it Yes, we can see what's going on paste in my new formula. My, if functioning okay, really is there. Click OK and close that books down. That should now work. See if I choose. Homare brings home a I choose Laugh Briggs Olaf by delete a name. I get a blank image. No image returned. They're going to use Homer again. Back comes home up, and so one. So just added depth, extra function to my look up. Get rid of that formula down there now. I don't need that anymore. Otherwise, I have a simple picture. Look up great for bringing back images of products or images of customers. Logo's looks great on your invoices and your forms and reports that you have if as well as the dynamic values and calculations and is to be able to bring back images as well something else that index can do. So it works, says my example. Number two 4. Example 3 - Return the Value from the Last Row: hello and welcome to this lesson on example Number three off the index function where we will look at returning the last value from a Rome and the same technique could be used to return the last value from a column if needed as well. So the example we've got here is a simple list of payments, and I would like to return the last payment that was made. So that will be, in this example, the last rope. This technique can be really useful with creating dynamic data reported where you may want to compare the last two values that this week of the last week on this kind of behavior we're gonna do similar things in the similar techniques in the chart lessons off this course as well. Were you really going to see these effects take fold? Rolling charts and monthly vary in season stuff here. I just want to return. The last payment will be really easy formula to be honest, Asia in the art last two examples. So e to start up the index function that's gonna ask me for the ray and I'm just gonna say this column B Look at all those payments I don't know how many there are comma that'll probably for a row number. And I want the last road. So I'm going to stick in. I count. Are you function which counts all non blank cells. So if you have a blank in there, that's not gonna work so well. You may need a camp blank function as well, in addition to try and get around that. But assuming that you don't have any blank to have in a range because he got arrange a payment of blank cesspit, possibly bad news, depending on the payments off. I'm gonna put my open bracket now and just tell it to count all of column B so that will find out there's eight of them. Therefore, return a value from the road to close brackets one for count, a one for index Presidenta returned one to weigh the last payment, and if I was to put in a new payment right now, but do control semi colon today's date and put in £1.234 on a press enter 234. It returned now as the last value in that range for the last row off my list and that is it a simple index function? With account a two count, the bottom will return the last value from a bunch. 5. Example 4 - Create a Dynamic Named Range: Hello. Welcome to example, Number four off the wonderful index function on. In this example, we're going to create a dynamic named range. So we want arrange toe, automatically expand and contract as a rangers get larger and smaller so that we can use this named range in our formulas. Now conditional formatting ranges at pivot tables that charts etcetera so they are fully automated. As the data range changes, everything updates itself. Now we're going to see to dynamic named Ranges here as well. I'm going to begin with a one dimensional range in column A. You can see we have a list of employees. You may recognize the names as tennis players. Hey, our employees right now, Over on the right, you can see I have a two dimensional named range. I have to find the last row. I also had to find the last column in that example. Let's start off with the first range on. I'm going to type this formula into a cell so that you can see what I'm doing every step of the way. I wouldn't copy and paste that into the named range when I'm done to create it, so let me start by typing equals and I'll slip the first sell off my range. Now I'm going to choose. Are you one here? And I could easily have chosen a to go. If you want to avoid the header, which you might want to do in specific formulas or something, I bet it's assumed that I want them involved. So click on a one I'm going to type in a colon, which I'm going to need just tied that up a minute. Don't need that bit. And I want to make that reference absolute. So I haven't absolutely dress there. It close dollar. I don't one colon at the first half off a range. Now the second half of that range is what needs to be dynamic. Where is the last cell last rope? And that is what indexes during. So let's bring that in and you know, the job of indexes to return a value or reference from the intersection of a Roman column on. We want to use that reference right now. The a right. It's going to be the entire column. I That's what I'm using right now. Coma. Now the row number. That's what I need to identify, and I'm going to bring in the count. I e. Function for this counts all non blank cells so ever their numeric where it's a formula, whether it's text, it will get counted. I'm going to provide that with column I and then a closing bracket from my count, a function closing bracket for my index function. So the index function is going to return the reference from Call my from the last so that county's gonna count them and go Oh, there is eight that will retreat a value of IIT making it I want to eight as a reference. Now I'm going to take a copy off that formula. I'm going to press escape to come out of that cell, go to my formulas tab on my define name button. I need to give it a name, So I guess I'll call it employees and then, in this refers to area, simply delete whatever is in there and paste in the formula that I created and that have now copied by clicking OK to that. I now have that dynamic named range. So if I was to click in this name box and type in employees and presenter. It selects Detroit area. And if I was to create one more, I put my name in there and now click up there and so go and fetch employees for me. When a press center, it includes the additional cell. So it is dynamic his automatic Pick that up. And if I'm using that range in my charts Maira pivot tables, my formulas, they are also therefore dynamic. Just to mention The reason I didn't use this little drop down on the end, which some of you might have been thinking when I was typing in employees, is because that dropped down Arrow does not show a named range when it's been created with formula. She see my employees does not feature in that list. So to do that demonstration, I had to type in simply done. You know, you would normally do that stuff probably involved in your formulas and that anyway. But I want to clear that up in case interview of thinking. What did he just press the little where only and who was he to happen in for? Okay, so that's the first example. Let's have a look at how we can create a to dimensional named range. Okay, so for this example, let me type. The formula into a cell will gain, so it's going to begin in a similar white. I would type it cools and click on the first cell over that dynamic range, and I'll put in my colon and just make that reference obsolete. Absolute story. So this time it's cell D one that's going to be the first sell off that range, and I've got my colon on. Now we just need to find the last cell, and this time it's F six. I need to find the last column, and the last row is F six right now. But I need that calculated. So incomes, the index function, and I need to provide that initial awry to search him says, going to be a little bit different from before where I just gave it the entire column A. Because that was one dimensional. Now, then to put in cell D one, cause that's going to be the first sell off the range to check. So avoid everything from comes a to see. That's not important. I don't need to give it the lost sale of arranged to check um, so, I mean, you could pretty much do anything here. You just want to be careful not to. I go to short. So, for example, I could just type in the range, putting my dollar signs for absolutely here. Offset 500. Yeah, I could do something about that. And you can see excel changed the color off the cells behind it. So I was looking within a much larger range to what's company going on. You know, she could go really excessive here and said that 10,000. It depends how larger range. Maybe your rain just may be much, much bigger than that at mine at the moment is only six rows. So let me just go for maybe 500 for now, for this example, Is it 500? So I've got a lot of scope. It get much wider this table on a lot of scope for getting much longer as well. How? Then put in my comma. So now we need to find the last row on the last column. Once again, the count IE functions are coming in to do this. What am I gonna count within? It's going to be column day Let's give it column day. I'm typing in this time for some reason. But whether you click the sale on pressure F four key to make absolute, I'm just simply type in the scene at the moment are they need to provide the column number . That's going to be another count. I and once getting kind of provided with what you want in this example wanted to exclude columns A to C. Now they will throw off. So I'm gonna start from D, the one that, in fact, let's be specific about my cell and I'll just give it I guess Columns ed to be consistent with the initial range that I gave it. And not that that matters that much. And he could see how excel changes the color of the ranges you're referencing really helpful to see exactly was going on to look down D Look across Row one, basically from day to shed close bracket, account a close bracket for index and take a copy off the formula put out of there going create a defined name formulas Tab Here, give it a name like sows and paste in the formula. Okay, And it goes, Oh, click OK, that's created now to give it a quick demonstration of it. Working, Let's say I had a V a cup function on. I was looking for a value this in Hates to Currently nothing will come back and do that within the table called South. Notice it come up as an type in here with the named range icon from Column three says column F right now Return the amount False Exactement. So good little V. Look up going on here. That's gonna moan because I do not have anything in so height to right now. So let me go. Type something in like free 16 and that returns the value off 2 to 7. Looks for free 16 returns. Third column 2 to 7. So that clearly works. Let's check out of its dynamic, Let me go and cracked New item. Let's pretend I have an item I d 500. It is a hot dog. I really fancy hot dog right now on the value will be 800 God said when I was hot dogs and then it's going back to Harvey. Look up and type in 500 in Cel hates to and when our president tough I 100 is returned. So I know that my cells dynamic range automatically expanded to pick up the extra row that wasn't there when I initially created it. And if I had created my vehicle cup by reference in cells at that point and doing it this way that would know have worked now, yes, there are other approaches to crack in that affecting things like a veal cut formula. I'm aware of that. People would highlight the entire columns and stuff like that, but I've got this two dimensional fund last column and the last row I've gone easy to remember name associated with it as well, and I can utilize that in any char, any formula that I use. And there are pros and cons of this technique of these techniques. Story. So there is an argument that that dynamic range is the ultimate, if the easiest to use its automated and reliable. So that is example. Number four index function used to create a dynamic range, extremely useful stuff 6. Example 5 - Sum the Last 6 Values Only: hello and welcome to another brilliant use off the index function this time how to total the last six payments only. So it's another dynamic example, but this time creating a role in effect. I only want to some the last six values the most recent six values. Obviously, this example can be inducted to average the last six or count the last six, or to some the last 10 whatever it might be for you. So this is a very typical effect, really. Now is payments in this example, and it's column billions. Well, that we're interested in. So in cell E to let me begin with some function, I am looking at totally nous so need a son. But then I only one of some the last six. So index function is introduced at this point, and I need to find to sail. You know, the first sell off that last six. So in this example, right now that is a B seven. That's the cell is B seven, but I'm not gonna type pain because this needs to be dynamic. So I'm going to provide the column B look down column B and bring in the count a function to locate the row in Kobe, but then asked it, Account a function. Take away. Five simplest AC count was in column B so that all returned 12 12 is how many things in column B Take five off that seven. So that will always be the first sell off the last six. I can close off my index function, type in a colon and then go for another index for the second part off that range. The array Once again his column. B Another count. I function. I'm column B, but this time after that will close off the count. A function. Let me quickly make that absolute at a habit, really an enclosed off the index function. I'm not going to bubble with any take away fire or anything in this example, because I am interested in the last one. So that will find Cell 12 that is the last one B seven to B 12. I need one more closing bracket here for the some function. And if I totaled that up, that is 997 and if I select the last excels, but then if you can read at the bottom But 997 is the answer. Let me just show that on screen with a typical some to free 12 free. It's No. 97 That's what we need that if I was to click in the bottom right now and put in a new one type indict, they put value in, let me just take in 100. It's easy to say in a press enter. I now have 897 and if I check out that, that is correct. But do the normal some free free? It's 897 So despite additional rose been added that is already picking up the last six. Wherever the last sixties company. Let's be eight to be 13. That's what it is right now. That is how we can create another dynamic formula. But this time, instead of picking up the size of the range like the previous example would be in specific and finding the last six values or the last 10 values, or maybe the 1st 5 values or whatever it may be for you, this could be adapted this formula very easily to achieve that task, and obviously any formula could replace some. Right now, you may not wish to add emails to average account or major? No, it could be partner V. Look up wherever it may be. 7. Example 6 - Retrieve a Range from a List of Ranges: Hello and welcome to example Number six all over the index function where we now look at retrieving a range from a list of ranges. So when somebody enters at London or Dublin or Paris into Selby one, I want to toe told of values from the correct table. Now, before we get going with this very important to mention, three named Ranges have been set up doubling cells. No surprise. The five sales value to Dublin London sells the fight for London and Paris cells to five Paris. They have already been created in whatever way that has been done. What I now need, though, is to retrieve the correct range name, depend on what they choose and Selby one. So let me zoom in a tiny bit here for this demonstration and start up. I some function because I'm totally in again in Selby too. So it prompts me for the range of values. That is what Index will do. That is what they will retrieve. Is there going to ask me for the awry Now, in the second bottom here, the second version of the index function. That's what we're going to do this time. You see they have a final question for the area number. Now that's what I want. Each area Dublin, London, Paris has to be referred to numerically and in this reference area, I can provide multiple references. So I'm going to open up a set off parentheses, a sort of brackets, and I'm simply guns type Dublin South Coma, London Ciouse Coma. Harris sows some providing the list of ranges. Outside of that, I can put my comma to move on to the row number argument another comma for the column number argument and then again to get into the area number. I'm not interested in row and column in this example. I just need to provide the list of ranges on now, specify what one I want now this it dependent on what is chosen, so be one. So I'm going to put in a function here to do this. There are a few different logical functions you could choose from going to keep with it for now, something that you guys watching. It's a party most familiar with. So start at my function. I'm just simply test that if so, be one is equal to the word doubling, then return number one. So that is the first range in my list Over here. Dublin, London, Paris, we said. Dublin Return number one. Remember, this argument is the area number that's one London's to Paris is free. So if it's not Dublin tested again, then is B one equal to London. If it is put to zoomed in a little bit too much here, and I let move across doing very successfully, let me start over another if and say, look. If that cell he's equal to Paris, then return value free and they're going to close off that if the second if the first if, as well are they need to close off the index function, are they need to close off the some function. So a lot of brackets on the in there because of my three ifs, plus the index plus the sum function all nested within each other. Their four functions nested in some by press enter. I've got Paris written at the moment, so that will total up. Paris Over here. 4436 Let me demonstrate that for some, the Paris values 4436 But if I go and change that to London, it sums London We're going to change it. Doubling its some stumbling. Let me just proved that it is work in some doubling values for 796 So this example shows a index function. Being used to retrieve a range from a list of possible ranges can be a very useful technique. Crank, conditional look up tables and conditional some in areas. No conditional charts. Such a variety off uses for this extremely flexible function, which have tried to demonstrate for them various examples here, culminating I'm in this one, possibly the least a likely scenario you're going to find it used in. It just shows them she versatility off index and what it could offer you annual spreadsheets.