Look Up Values with INDEX and MATCH functions in Microsoft Excel

hi there Jamie here today at Teacher's Tech  hope you're having a great day today! today   I want to show you how to use the functions  index and match in Microsoft Excel so these   are two separate functions in Excel but when  you put them together they can become very   powerful so let me give you a little demo of  what we're going to create today so let's say   I have some data like this and this is just  some different streaming services with their   quarterly Revenue with index and match and  a drop down like this I can go and choose   let's say if I was looking at a different one  I was choosing Netflix in quarter three comes   back at 7. 43 billion dollars if I look at  Netflix over to the quarter three and you   can see it pulled from this array right here  so I want to show you how you can use index   and match these Microsoft Excel functions  to create this here today on teachers Tech so if you'd like to follow along with today's  tutorial I'm going to put a link to this   spreadsheet Down Below in the description then you  can click on it download it open it up and follow   along with me all right let's get started on how  to use index and match in Excel now actually the

first thing I'm going to do has nothing to do with  index and match I'm just going to do a couple drop   downs create a couple drop downs of the different  things so I don't have to type anything in and   that's just using data validation I have separate  videos on that but in this spot I want to be able   to drop down on the services here so I'm just  going to go up to data in my tabs here and I look   in the ribbon and here is my data validation right  here this one so data validation opens what do I   want well it's going to be a drop down and it's  going to be the list so list what's my source of   this list I'm going to choose this and my source  is going to be just the streaming services right   here so I'm just going to hit enter and then hit  OK I have my first drop down just like that   so I also want to be able to choose the quarter or  total here for a drop down so I'm going to click   in this cell and go back up to my data tools and  choose this one again and I'm going to drop down   choose my list pick my source and choose what  they're going to be their options like this just   hit enter and hit OK so now they'll be able to  drop down and choose what quarter or total they   want for each of these okay now the first step is  going to be using the match function now I want

to put the match function right here to begin with  and so the first way I'm going to do this is just   moving up to the function insert the function tool  and this is where you can do a search so I could   type in you can see matches already at the top  because I've recently used it so I'm just going to   select this now this walks you through so what's  going to be my look up value for this first one   well my lookup value is going to be what is picked  in this cell so if I click on this cell so C9 you   can see that it equals Netflix if that changes  then it's going to equal something else so what's   my lookup array so the lookup array is going to be  this over here so I'm just going to hit enter and   what you see so far so it all the match is going  to do is give me back what where it's located so   if you look at the result is number two here so if  I look it's Netflix is in the second spot   so all it's going to be giving me back with match  is found that it's going to be in that number two   column right here so I'm just going to hit OK and  go from there now this doesn't do anything what I   wanted to do by itself but we're going to do one  more of these matches and this is going to be for   quarter Okay so with the quarter same thing this  time I'm just going to type in my equal sign and   go to match this way so I'm just going to select  match what's my lookup value well it's going to   be this spot right here and so I'm going to put  a comma in what's the lookup array it's going   to be wherever that is in there so now this should  give me back so if you look what spot Q3 is and it   should give me back three on this one because one  two three four five there's five all together but   this is in three let's hit enter and it gives me  back three so again by itself you're thinking no   this isn't really providing me anything here but  what I want to be able to do is use index with   this so this is where it gets more powerful to  be using these and I'm going to show you a couple   different ways where we can eliminate these where  we don't want this showing we don't even need it

when we Nest The match function inside  indexed so what I'm going to do in this spot and   I'm going to hit equals again and this time I'm  going to use index on this I'm going to type index   and you can see comes right up there now you could  go up and insert the function like I did the first   time so what is the array that it's going to be  looking up so where's the information going to   be coming from that is all through here so this  needs to be selected this is where it's going to   look through and try to find it I'm going to place  a comma in here so what are my row numbers well   actually my row numbers are right now if I wanted  this I could put the number 2 in and number 3 in   here so I could actually type those numbers and it  will give me back so if I go 2 comma 3 so just to   go back and I'm going to go back a few times and  alter this and hit enter it gives me back 7. 43   you can see Netflix over here in quarter three it  gave me back the correct one but the problem is if   I change this these aren't linked so something  I could do is go back and reference the cell so   that would be easier because then I could change  these so what I mean is rather than putting the   second and third if I go back to this 2 right here  and just click in this so this becomes D9 and then   on this one right here this number three this is  going to become d10 so I hit return so everything   looks the same the nice thing is now it's linked  so as I choose different one so if I was looking   at Tick Tock total 4. 69 is that correct and if  I go up to the total of tick tock there it is   so this is just rounding a little differently  from this one but it is pulling out this number   right here so this makes it nice because whatever  I change here it uh it will reflect down here

is more Dynamic let's say I don't want to use the  cell references what I did here I want these cells   not to be here at all so what I can do is Nest  the match function inside the index function so   it's just going to require a cop a bit of copy  and pasting but it's not super complicated so   if I go back to here and you can see here is  my formula right here that I've created with   the cell references to d9d10 if I go let's say to  this first one here I can copy this match formula   here so I'm just going to go to here and I've have  that selected I'm going to go Ctrl C for copy Ctrl   C and hit enter so to get me out of this now I'm  going to go to revenue and rather than referencing   D9 I'm going to put that formula so I'm going  to go and just highlight that D9 and I'm going   to go Ctrl V so it's put it into the place where  D9 is I'm going to hit enter and there I have the   first step notice nothing's changed yet because  I haven't moved these numbers now uh here if I go   to this one I need to take this so I'm going to  highlight this again Ctrl C for copy Ctrl C hit   enter so you get out of it so you don't start  clicking all over the place and changing the   uh the formula now what do I need to replace well  this time it's d10 so I'm just   going to highlight d10 and do my control V for  paste and now I have my two matches nested inside   my index function here so I'm just going to hit  enter again nothing looks like it's changed now if   I go and change you can see everything is making  the change here but now if I delete these just   like this they're not being referenced anymore so  now it's all within this formula down here so if I   go to Netflix you can see how it's changing from  here so I could actually move this spot so   let's say if I wanted this to be over here I could  go to control I'm just going to cut it out Ctrl X   to cut it and then go over here and just paste it  over here I can go back and I can modify this if I   want to put the dollar signs on here maybe bold it  you can do those things yourself but now this is   the using the index in match function so it's the  nested in nested match function inside index that   will be able to use any of these to find what I'm  looking for so I hope you like this tutorial here   today on how to use the index in match functions  here as I said together they can be a powerful   very powerful way to manage inside Microsoft  Excel you can use other methods to you know if   you're looking at vlookup or X lookup to search  for different things I'll put a link to my X   lookup one because that's a nice powerful function  too and you can do a lot of things to it thanks   for watching this week on teachers Tech I'll see  you next time with more Tech tips and tutorials