Excel Magic Trick 778: INDEX & MATCH Lookup Functions Beginning To Advanced (18 Examples)

welcome to Excel magic trix 778 hey if you want to download this workbook click on the link below the video you are not going to believe this video this video is all about index and match the most powerful lookup functions in Excel they can do all sorts of things that vlookup can't for example hey this video is actually going to be quite long totally extensive I've done lots of individual videos even some somewhat comprehensive videos of index and match but this one puts it all together that means it's going to be long like an hour long if you click on below the video there's a description if you open that up it'll have this table of context and it will tell you the minute mark in the video where you can find these topics now match and index we're going to start with the match function so I'm going over to the sheet called 1 now what is the match function do match function if I'm here and say hey look up Joe it'll look through here and say hey Joe is the second item so this is what's called an exact match and you got it in the third argument put a 0 so let's look at this match lookup value I'm going to look up Joe comma lookup array this is going to be a one-way array whether vertical like this or horizontal I'm going to highlight this I'm going to hit the f4 key to lock that range lookup array comma and then match type we're going to say 0 for exact match that means this doesn't have to be sorted now it will look up and give us two because the relative position of Joe in the list is 2 now let's copy this over and talk about the different situations that you can run into I'm going to copy this over just to here now only 1 enlist well it's going to return a 2 but what if we have dupes this formula right here is looking up Tom oh there's two of them no problem match will return only the relative position of the first item now that'll come in handy and some of our more advanced formulas knowing that duplicates it finds only the first one what if it's not in the list well it's going to give us an n/a for not available now sometimes this is just what you want we'll see an example of this also you're in essence saying with n/a I look this up and this item is not in this list finally match can do vertical or horizontal which makes it pretty spectacular as a lookup function for example vertical vlookup can only do vertical so I'm going to look up Jo comma and I've orientated the table sideways ctrl shift right arrow and then f4 key comma zero close parenthesis so it can do vertical or horizontal it's only finding what not really a row or a column it's finding the relative position so Jo is the second position in both the horizontal and vertical range now let's talk about match when you have the third argument one now this is often used for things on hey look up someone's income now match all match does this tell you the relative position but later we'll use this exact trick for looking up a certain tax rate but notice there are what distinguishes this table from the other there are gaps there are numbers between 0 100 which we would like to find and we would like the relative position for example if it was 73 I want the relative position one down here if we're looking up for um so yeah this one's in between so for say look up 150 it will find a number in between here and no to go to this row or this relative position here now a couple things about approximate match with a 1 the table has to be sorted in ascending order now here's what it does equals let's just see how to do a match I'm going to say look up 150 comma within this range right here f4 comma now we could put 1 or if you know that the default is 1 you can leave it out so I'm going to backspace and I'm going to leave it out now let's just think about this why did it return to match when doing an approximate match with the final argument number three argument equal to one or default what does it do it takes this hundred and fifty it keeps it in spray and it goes racing through the list and when it bumps into the first bigger value it jumps back one that's why for this particular look of value it gives us two now you can imagine an income lookup situation that's exactly what you want because really it's this category right here is a hundred dollars yeah greater than or equal to 100 but less than 500 so that's why the metaphor of look this up bump into the first bigger one and jump back works really well now let's copy this over and see what happens for fits into the gap that means it can find a number in between exact value no problem this category here is in essence 100 right so that's how all these categories work this is the same way that vlookup works too by the way though this match with approximate match with a 1 or left out is exactly like vlookup right so this number is included but the next one is not that's of course so we don't double count if we had equal signs in both places it wouldn't make any sense all right so it can find an exact match here it says 2 if you give it a number smaller than the first one again the table has to be sorted you go beep it hits that one and it goes oh this number is bigger than it just says not available bigger than the last one no problem for a lot of tax tables it's you know anything greater so the x value would be greater than or equal to this and whatever so anything bigger gets this last category as we can see here 1 2 3 4 5 6 7 finally just as we saw before match can do is totally amazing because it can do vertical or horizontal and I'm going to leave that last argument off actually actually this one I'll put in a 1/2 does the same thing I always like to type less so if I'm doing this all the time I just leave it out and that of course gets one right jumps into the first bigger one now let's see this is the more common one whoops sorry one as the one or left out for this match type but let's look at what minus one does and really this is the thing that can distinguish match from vlookup vlookup can do an exact match or approximate match but what vlookup can't do is this now this minus one and the third argument is when you have the table sorted descending and then the logic is the same as one approximate match except for you bump into the first smaller value and jump back one now this is a rare type a lookup but it definitely does occur so let's see how this works match I'm looking up this comma I'll pick the vertical one f4 to lock it comma and minus one now this is less than or greater than and if you read help it explains that the way I like to think of it is the bump your head into metaphor right so I'm going to put minus 1 that means now I'm going to look up a thousand five hundred when I find the first value small or then I'm going to jump back one right so this should give us three takes a thousand five hundred it's been a little bump goes there it tells you relative position three now I'll copy this over here of course it can find an exact value right so two thousand five hundred is that if it finds a bigger than first remember up here it was smaller than first well looking up here so here when it finds a value bigger than this it just goes not available' smaller than the last item no problem it can go way past here this category can be 0 or anything smaller so it finds 7 and then of course the beauty of match so awesome it can do three types of looks at lookups and it can do horizontal or vertical minus 1 close parentheses so there it's finding a comes down here runs into the first smaller value and jumps back one so those are that's a lot of examples there I said there was like 18 examples but really that was one two three four five six twelve that was already sixteen examples of match now let's go look at what index does I'm on the second sheet index does a lot of things we're going to start off by looking at two way lookup now here we have a part ID and I'm going to pick part two and a quantity I'm going to type in 15 so what we do here is we have to come down here we find part two and we go over we know this is the row relative position two but the index will need to know that this is a row position and then the two way look up what we have a variable here too so we go here here we bump into the first value bigger than and we jump back one so we know this fifteen should fit here so it's this column number so really if we're looking this up the row number is two but match will just treat it as a relative position but we will have to put it into the right argument for index row number and for looking up this fifteen we'll go one two oh that's the second column or relative position two and this is the value that will be returned so the one of the main things that index can do is it can do two way lookup now there's lots of other things they can do and we'll see this throughout the video but the basics of is is just that so get this we're going to do two matches match this comma within this vertical array comma 0 that's an exact match that says - and then here match we're going to do lookup this 15 comma within this horizontal range totally awesome I'm leaving the argument off because I'm doing approximate that's where I want to bump into the first bigger one so there we have it if I change this to 75 then I got of this by the way I did this with conditional formatting I have a video if you search YouTube conditional formatting intersection of row and column it'll show how to do that and then here is our index now I'm going to do a little trick here I'm going to copy this and I'm going to ctrl CC that opens up the clipboard if you don't have that turned on as an option then just come here and click this so you can see that one match that's for the part number I'm going to come over here and in edit mode I'm going to copy and so I have these two things remember that's delivering a row number that's know this is the row number this is the column number so equals index index there's two different types of index the first and most common one is give me the array and we'll see later that that array means you can also put actual arrays in here and it will understand it but for us it's the table the two-way table now very important when you're doing index this table this array includes the values only unlike vlookup where we include the first column you do not do that with index the columns and I mean the row headers and the column headers will be used inside of the match function to get us row and column so the array just the things now this is a two-way right it's not just a one-way it's a two-way it's got a row position and a column position now comma the easy way to understand this is just a hard coded and right I want row to comma and then the column number is row 3 now you never do a formula like that but that's the essence and really how simple index is when you're doing to a lookup right and it will return that 18% but of course we don't want to hard code this in so this two really needs to be replaced with the match and this illustrates the real power of match and index together because we can do two-way lookup which vlookup can't and match allows us to do three different types of lookup so when I enter that will work now whatever I change here will change the look up all right so it's returning 17% all right so you got it two way lookup now XMS can easily do one-way lookup will first look at vertical so match can do look up with two vertical ranges in addition we'll see here how to look up left now look up left is hard for vlookup to do there is a weird type of formula where you mix it with choose function but really if you're doing left lookup you should index is a great way to go so I'm going to say index the array the things we're looking up you know let me explain this first why would you ever want to do lookup left well one reason is you're not allowed to put the lookup column as the head column here we have product price and description right so I have boomerang someone comes into the store they say what they want a 50-meter boomerang so I want to look up the actual name of the boomerang so this is an example of where you'd want to do lookup left with two vertical ranges so the array it is going to be the actual values to look up and those are vertical comma and the row number well here we have to do match and the value we're looking up is that comma in this vertical array it is exact so comma zero close parentheses close parentheses and that is beautiful we could do a one-way lookup with two vertical ranges also doing lookup left so now when I say 50-meter I just say hey customer you want this one right here you want a hundred and sixty there's only a few people in the world that can throw 160 meters but there are boomerangs for that but we better take boomerang nine so that's lookup left and look up to vertical ranges now let's look at our next example here this is a one-way lookup with two horizontal ranges now one of the ranges will be a locked range and one of them will be a relative range now here we have some vendors and some products and we've gotten bids so for product four we have a bid from vendor one vendor to vendor three vendor for vendor five well here's the smallest bid and we just want to know which vendor it is so I look up 32 in this range it's one to the third relative position and I'm going to tell the index that this is the lookup array so I want to return vendor 3 to right here now I just did the min function here right you could do it in a separate column or you could actually put it into the the match function all right so ready here we go equals index and I'm going to put in the array a horizontal range and I'm going to lock it with f4 key comma the row number match now wait a second this right here says row number shouldn't I go comma and then put the column number in it will work that way but index is programmed once it sees all an array right that's the the items we're looking at once it sees one way which means it only has one dimension right there's one row multiple columns all you have to do is put a number here so even though it says row number we're actually looking up a column right the third column in this these values here are number three that's the column we want so you'd think we want this but it's programmed this row number if it's one way you just put your relative position in row number so I'm just going to do match I'm looking up the low bid comma the lookup array these are relative as we copy the formula down we want the dancing ants to move but the array that we're looking at values in needs to be locked and this has to be an exact match because these numbers are not sorted now we could run into a situation where there's a duplicate here and we saw earlier match with exact match we'll only find the first one now there's a few ways you could do this you could do an advanced array formula to extract multiple records when there are duplicates or you could simply add conditional formatting here again I have other videos on conditional formatting for any way you do conditional formatting for duplicates so I copy this down and sure enough if I edit my formula and look sure enough this purple range here is move down as a formula goes down but the actual values in the array that we're looking up is locked so that's a great way to use index notice there are two horizontal ranges our next example example five this is a rare occurrence but it does occur from time to time index and match have no problem looking up a value in a vertical range and returning something from a horizontal range so I'm going to say equals index remember the array here is always the things you want to return I want to return boomerang I'm going to click on that cell control shift right arrow and then scroll back we're not copying these anywhere here so we don't need to lock them comma and then row number remember it's a one-dimensional array it's a one-way lookup so we only have to put a relative position into row number so I'm just going to say match and I'm looking up this comma in this these have to obviously be the same size and it's an exact match and then we control enter or control enter and we get boom three so if I say hey this person wants a 20-meter boom that will be boom to ten meter that's boom one so this is just an example of we have this was where we got our match which was vertical and this is where we got our return value which was horizontal alright our next example or to come over here and we're going to see the -1 example let's click in b5 and hit delete and actually let's close this - all right so here we have a two way lookup we have selected rainfall and pipe size and from that those two inputs we want to figure out the square footage that this pipe will handle so and for whatever reason this is sorted this way and we're not allowed to change it no problem match can come to the rescue because here we have sorted in descending order we put that in it bumps into the first smallest which is 11 right and jumps back to a 12 inch pipe which would be fine if 11 can handle it then certainly 12 can so we're going to do we have a two way look up so it's equals index the array remember when you're doing two-way lookup you highlight just the values that are potentially can be returned to the cell not the column headers or row headers comma the row number well that's going to be this row headers right match and I'm going to look up the actual pipe size comma comma and then minus one and then our next match will be looking up this now if we could do this one of two ways well comma that's the lookup array will be this this horizontal we could just leave the default noticing that the numbers are sorted from smallest to biggest you all could also could do exact if you wanted but I'm going to leave it like that the default is a approximate match when the table is sorted from smallest to biggest ascending I'm going to close parentheses there's our column number and then enter so if I change this to 13 you can see it should go to this one and then for inches so it should be over here I change this to two and change this to 5 and we should get here's a 5 and there's a two all right so we can definitely have the minus 1 when we're bumping into a smaller about value and jumping back 1 our next example we want to see how to use index and match to lookup not an intersection of a column and row but actually look up a whole column or in our next example to see how to look up a whole row now this is important sometimes when you have some some data and you just want to add either January February March and you want to be able to change this to February and have the total update now the index can easily look up a whole column instead of just an intersecting value so equals index the array is going to be this comma and here's the trick if you're looking up so right now we want to look up fabula and we want this well notice there is a column it's column one two and there is a bunch of rows all the rows so when you get to row argument if you want all the rows you either put a 0 or leave it blank I'm going to leave it blank comma so it says 0 or empty so I just left it empty comma now the column number we have to do match there and I'm looking up this comma within this comma and this is I'm going to use an exact because we have words and they're not sorted potentially in the right order they're not because M comes before a by the way if you had words and they were sorted perfectly you could use a proximate match but approximate match means there's potentially values in between and there's not any values in between so I like to keep it as zero for exact match it's that right there now if I hit enter it doesn't know what to do because it has four four four it doesn't know how to display four values in a cell so we have to put it inside a sum function but watch this you can prove to yourself that it's actually looking up all the roads highlight it and hit the f9 key the f9 key evaluates you can see it in fact is returning four rows semicolon in a race in taxes is a race in tax curly brackets contain the array semicolons mean rows columns mean commas so if it were if this were displayed across the columns it would be a comma so this is row that goes down to the next row all right control Z don't you don't want to f9 and leave it there undo is control Z and then some you need to put it inside of a slips I just click there by accident and put that there I'm going to control Z controls the only works for the last action when you're in edit mode very carefully click out here close parenthesis and you don't need control shift enter just enter now let's test it I'm sure enough it's it's looking up the whole column now you can imagine that how do we do this for rows equals in some index I'm going to highlight the whole range and comma we actually need a row number here because we're looking up the row and we want to return all the columns so the row number is going to be match and I'm looking up January comma within this range that match will return the relative position comma 0 for exact and then there's the row number you've got to be sure and type a comma get to the columns and 0 or empty means give me all the columns so it would give me column those three columns I'm going to highlight this in f9 it just that part f9 you can see it shows commas the way I remember array syntax is comma starts with the C and columns start with the C control-z close parentheses and enter so there we have 5 if we change it to February boom alright now our next example so we can look up whole columns or whole rows our next example you're not going to believe this index and match can actually look up a cell reference now what in the world well looking up cell references sometimes can be handy when you're doing dynamic ranges for example I have some data here and I want to make a chart but I don't want to I don't want to have all these blanks here showing up in the chart so I want a dynamic range right now the chart should look here but when I add a new of a new value down here or here I want it to expand automatically so that's where dynamic ranges come in and what are we going to do we're going to look up a cell reference so right now for all of these this range we're always going to have the first cell reference equal a 11 right but sometimes the last range cell reference in the range will be a 18 or a 19 or a 20 so we're actually going to have to look up the last value it'll be easy the row the relative position we can simply use the count function because dates our serial numbers are actual numbers so we can use count which counts numbers and say how here it will say how many are there it's there's eight but that'll be perfect for finding the relative position of the last item all right so let's see how to force index to lookup a cell reference let's just see how cell references work it knows I'm sorry ranges works a cell reference a 11 and then there's a colon and then another cell reference that colon is what is going to be the trigger to tell index I am looking up not a value but a cell reference now here's the way I'm going to highlight this right here and hit delete notice there's a cell reference and a colon now we're going to put the index here and the fact that the index is preceded by a colon and a cell reference the index is is instructed to look up the actual cell reference instead of the value all right so looking up the array now there's a couple ways you can do it some people highlight the whole column like this some people highlight on a bunch of cells they just make sure that they go way below where any data is ever going to go now for the video I'm only highlighting these not only highlighted 4 cells more but you know you might have a 100 more 200 have them any the maximum number so there it is right there I'm actually going to hit the f4 key I'm going to come back here and hit the f4 key we're going to need that those locked later for our dynamic range now comma row number well these are numbers and I'm anticipating that we won't have any blanks so I'm simply going to use count and I'm going to use the same ranges here exact same range so it'll always count and count will just give us 8 which is exactly the relative position of the last one there's the row number close parentheses we don't need a column number and watch this if you highlight just the index not the colon or the other cell reference and hit f9 notice it's turning it's actually returning the value that is a serial number there that's a date format on top of that serial number so it's actually looking up I thought I just said it looks up a cell reference while control-z you actually have to highlight all of this because it was evaluated without the semicolon what's it when it's evaluated with the semicolon it knows to look up the cell reference now I'm going to hit f9 here and you see that it returns all the values so the f9 key trick to evaluate doesn't show exactly that it looks up the cell reference but you could see it's given us all the values ctrl Z I'm going to enter this with ctrl shift enter because it's returning a bunch of values then I'm going to type something down here now I'm going to come up here highlight and f9 you can see sure enough it's expanded by one and has that so the dynamic range is working now once I control Z I can't I mean f9 here I can control Z or I can go back to the previously stored formula by hitting escape now I want to show you one other way to prove that it actually is looking up the cell reference click in the cell go up to formulas and evaluate formula the keyboard shortcut is alt tu F alt tu F tough now underline I'm going to click evaluate underline you can see that's the index and it's looking up what is it looking up a 18 that's a cell reference that's looking up I'm going to click close now I'm going to copy this right here escape edit mode ctrl V now there's a few ways we could oops I forgot the equal sign there's a few ways we can do that we want the oh I did it backwards oh no I got it right this is sale so we want this one so I'm going to change all these to be be and then control shift enter now how are we going to get these two as dynamic ranges into our chart I'm going to use the defined name feature so I'm going to highlight this first one for date control see in edit mode escape to name manager or the keyboard shortcut control f3 new notice I already have some there because there's the answer here new by the way there's a lot of notes in this workbook if you download it the name I'm going to call it date and then I'm going to say down here you've got to be kidding me refers to define names can not only look at a particular range but they can also look at formulas any formula so I'm going to paste now and highlight this there it is right there I'm going to click OK now I'm going to test it and the way you test it is I'm going to click on that right there and sure enough you can see oh it is working look at that that's a cool way to test define names all right I'm going to click close I'm going to come here scoop it out in essence copy in an edit mode ctrl C escape control f3 new it's got the word sales how did it get that oh because mice it's thinking that I want that word right there ctrl V okay click this collapse button sure enough it look like it's looking alright click close now we want to create a chart and I'm just going to create a chart by highlighting that insert and you can pick whichever chart I'm going to click column now there is a keyboard shortcut for the default chart excuse me the keyboard shortcut it's alt f1 I'm going to point to the edge and make it a little bit smaller now the key to charts I'm going to click there and delete with dynamic ranges is we need to I'm going to delete I'm going to click here and delete just so I get it all we're doing assume that it's dynamic if I click on this these columns right here you can see there's a series function here and this series function is looking at all the ranger that's why when you change the data the series function picks it up and displays it in the chart but I'm going to go up to the chart tools design and select data this is really the power of charts because you can do all sorts of things add edit I'm going to click on sales and edit now here's the weird trick down in series values you have to highlight very carefully the cell references but leave the sheet reference part now I'm going to type the name sales or hit f3 because I forgot them and double click so what I've done is I've replaced the cell references with the name leaving the sheet reference when I click OK now watch this when I click back here and say edit it puts the workbook name in so it's a weird kind of double step there you have to highlight the cell paste the name click OK and the next time you look at it it will show you the workbook and a name which you can see the workbook up there and the sales click OK I'm going to do the same thing over here edit highlight the sells f3 double-click date click OK click edit you can see workbook name click OK click OK now let's test it I'm just going to copy this down I have a formula there and this will be 12 and sure enough it added that extra value now the beauty of dynamic names as opposed to the table features the table features sometimes has a hard time with contracting ranges but if I delete all these the chart is still there I come here right that's a silly chart but you know equals this plus 1 equals this plus 2 so now I can copy this down right and so as I copy it down each day or add my new numbers it just totally updates that it's just totally amazing totally amazing so this is too so absolutely beautiful and one of the advantages of using the index to look up a cell reference as opposed to the offset because offset function is specifically built to do dynamic ranges create ranges offset is volatile so anytime the sheet is recalculated offset recalculates and so index avoids some of that trouble now there are some dynamic range problems that offset can do that index can't but in this case I love this being able to look up a cell reference to create a dynamic range alright our next example we're going to go over to sheet 11 12 now example 11 and 12 we want to see how to use index when we have to look up from multiple tables now here's a product we sold and the sales product 3 a different product and the sales the thing is we need to calculate our discount but each one of these tables product one product two we have different tables for different products so this is a situation where you have different look-up tables now the first key we're going to see how to do this with index but then I'm going to show you how to do it with choose now I normally would just switch over to choose because it's more versatile but this is a video which shows you everything about index and match so I have to show you this option now no matter what you do whether you use index or choose to calculate your your discount from multiple tables you have to give each one either index or choose a table number this is going to be table 1 2 3 so you can either add an extra column or put whatever we're going to put here inside of the larger formula but either method has to have either a 1 2 3 some integer to say which table you're looking up so I'm going to add an extra column here and use vlookup now the lookup value I'm going to look up well I want to look up wait a second before I even did this I just thought in advance I have all these products so I made a little table down here here's our 3 products table 1 2 3 those are the integers that will tell our lookup function which table to look up the item in so I'm going to look up this remember this column is to just give us a number comma and the table array this look it up here and tell me which table I need f4 to lock it comma the second column has the number we're returning to the cell comma and this is true or false exact or approximate in match this would be 1 this would be 0 guess what I never put true or false this is exact so I'm putting 0 all the years I've been doing it and never had any trouble with that I put a 1 a 0 for exact match or I leave it off for approximate match because that's the default all right all vlookup is going to do is tell us which table now we can do our lookup with index and match looking up from multiple tables this will be a formula input into our formula that says which table equals index now normally we use this one up here but that's what this one down here is for reference that means all the tables row column if you're doing two-way lookup and the area number that's which table you want now the limitation to the index method is that's his reference what it means is the tables are formulas right here but all of these tables have to be on this sheet and they have to be references not arrays so now how are we going to put three tables reference you got to type in open parentheses and then you can put as many tables as you want separated by columns actually we want just this column right here every single one of these references are just going to be the actual discount amounts we're doing a one-way lookup in essence I'm going to hit f4 comma and then this one f4 comma and then this one down here f4 and comma no no that's one two three so no comma closed parenthesis now the order is important so this is one two three the order in which you put them in will determine which area number you want alright now I'm going to put a comma and row number now we need a row number so each one of these one two three four one two three four we need a row number so I'm going to use the match and we're going to look up the actual sales amount and that's a relative sevens comma and now I'm going to highlight this this is a one way and again the numbers here have to be exactly the same in all the tables so I'm going to hit f4 I could have put this off to the side I want or I didn't even have to have these repeated down here I could have had you know this over here or this over here also I set this up because in my next example I'm going to use choose and vlookup now so that's the lookup array comma and we're doing an approximate so I'm going to leave it off that's this one you can either put one or knowing that it's the default just leave it off so close parentheses that's a row number so we type a comma we don't need a column number but I do need this area number so I'm going to click right there and that's a relative cell reference close parentheses control enter and double click and send it down now let's do some checking so 9000 on table 3 so I come down here it's hitting the bigger than the last one so it's 1.5 and sure enough that is table 1 so we have an 8,000 table 1 we have a 2,000 so we bump into the first bigger bag so it should be 2 so it looks like it's working that's just absolutely amazing now if you wanted I'm going to put this in edit mode and control CC actually I'm going to clear ctrl C escape I'm going to highlight this vlookup ctrl C I have the vlookup right there escape and I could just come over here and in this column oh I guess I will put it right there I could put this I clicked on that and this formula is looking at that orange cell right there so I could simply double click that and replace it with the vlookup and then ctrl enter and copy it down and so ultimately that's what usually people do they don't have an extra column like this right so if I were to delete this this one doesn't work but you could see that this one does just fine now I'm going to ctrl Z Z now actually for both of these if we're calculating not our discount percentage but our discount amount let me close this you'd come over here and multiply this times that so it's doing a lookup not only is it looking up the table number right no I don't have that here just f2 there's the table it's doing a lookup for multiple tables returning the 2% to this formula and then multiplying it by that and so we get our discount you could do the same thing over here now I'm going to show you how to use choose I like choose better because you can by the way let's just look at one last thing before we switch over if I had a two-way lookup and both the row headers and the column headers were different right in this case we have a one-way lookup and the row headers the the value we're looking up to figure out relative position are all the same but if they were different and these were different you would actually have to put 8 use that use this and use the choose function here so that's one of the reasons I just I never ever use this index reference I just use choose function the choose function has two advantages it will allow you to do two-way look up when there's totally different labels and your tables don't have to be on this sheet they could be somewhere else all right let's see how to do this I'm actually going to use vlookup here and the reason why is I have these tables set up now I'm not going to change these numbers to be different but imagine that they were and this would work fine you can try that for yourself I'm going to do vlookup now ultimately we are looking up this value right here so comma now table array now here's the problem we have three tables right but no problem we can use the choose function now the choose function and I have lots of notes down here is just awesome it's going to have an index number which is the same as the area number in index it wants a 1 2 3 4 but these values you actually just put whatever you want right into the choose function separated by commas the choose function can do anything cells ranges define names formulas text all functions anything you want can be put here so this is really an amazing lookup function so the index number I'm going to click there comma and now I simply put my ranges in now imagine that they were these were on a different sheet and you just do a sheet reference you click on the sheet and then highlight them alright now I'm going to hit f4 comma now notice with the screen tip 4 choose value 2 I get the next table f4 comma vonda value 3 I highlight the next one so if you're on different sheets you could use either sheet references or define names so look at this this choose right here that is give it that's three tables right into the table array argument for vlookup now if you're using match and index you would have to put this choose into the Mac into the match because undo a two way in essence look up with the choose choosing the the array the one-way array for the match alright so there's a table or a comma it's always going to be returning from two so I put a two comma the lookup range by default is approximate sorted ascending so I'm going to leave it off backspace closed parenthesis and I double click and send it down now I'm going to multiply this times that to get my result double click and send it down alright so if you're doing multiple tables I have other videos a bunch of videos on that choose is the one I like the best it's more versatile because you can have the tables on other sheets and these the actual lookup column or if it's a two-way column the column headers and row headers do not have to be the same rank index all right now let's go look at another example 13 I want to see how to flip a table here's my situation I have a lookup table and for the report it needs to show biggest to smallest but I actually need it to function just like when I do look up I needed to function like it was sorted smallest the biggest or ascending so no problem I have this I'm going to use this to print out my report or I'm a teacher I use this to put in the syllabus but for vlookup I need to flip the table it's no problem we can use index and match the only trick here is we're going to have to as we copy index down we'll say look up here the first cell needs to have the number 6 because that's the sixth item so in this cell I need a 6 when I go down when I copy the formula down I need a 5 then a 4 then a 3 the way you do that is you use the rows function the rows function I'm sitting in and well actually I'm going to highlight this whole range right here a 2 to a 7 and I'm going to very carefully put my cursor in the a7 which is the last value now what does Rose do anyway what's row if it looks at 2 to 7 what does it see it sees six rows well if I lock this one and I'm going to lock just the row reference as I copy it down the last one will be locked but not the first one so two will turn to three so will give me 6 5 4 3 2 1 control-enter click copy down and over now notice totally perfect the cell references were intentional there are no cell references there justice little row reference there but that's going to be our in essence row number or relative position now I simply put this inside of index I'm going to highlight just this column comma f4 just lock the row references because as I copy down I want it locked but when I copy this formula over I need the dancing ants to move over comma very carefully close parenthesis we look up there's the array there's the row number control enter double click and send it down and copy it over so now I flip the table and what's beautiful about this is I use this in the syllabus I use this in vlookup if I come up and change this here to 4:45 boom that changes down there so again the idea in Excel everything that can change you put it in one cell somewhere and any other time you use it you refer to it with the formula I'm going to control Z I'm going to put it do vlookup now or index or whatever you want so vlookup I'm looking up Wow 401 that's a pretty good score table array boop comma - this is a approximate match sorted ascending and there is the decimal grade again looks up finds the first big ol Boop and jumps back one row so that's flipping a table our next example 14 I want to how to see how to look up the next bracket now here's the situation here this isn't Commission pay this is a mount if only I could type to get to next bracket Wow let me get to next bracket well I make it I just made fifty five thousand and my current commission rate is 6% but I want to know how much how many more sales oh I'm sorry this is here fit this boom so I'm bumping in there and jumping back there if I did a lookup right but really what I want is I want how much I need to make it into the next bracket so I can do this in my head right this - this it's 4455 well I somehow need to replace this cell a9 so that if I put any number here it moves well guess what it's easy enough to do some sort of look up with this value but what would it give us it raced down this is 5500 raced down here bump into the 10000 go back one row well what do we want anytime if we were going to use this to the lookup fact any time we find a row we want to add one well vlookup can't do that but index and match can so I say index these are the this is the range I'm looking it up in comma the row number or relative position match lookup value I'm looking this up comma within this and the match type is the default so I'm leaving it off and then that's a row number actually I'm going to close parentheses on this just for a month that - a3 is out there but let's think about this this row number if I hit f9 to evaluate one - oh that is so cool so match delivers an integer that we can simply always add 1/2 and vlookup least I don't know how to make vlookup to do that but match can do it no problem and there it is so if I change this to 37,500 tells me exactly how much I need to increase my sales by to get to the next bracket so that's just a great use of index and match alright number 15 we can select random names from a list so one let's select one name there's a function called Rand between in 2007 and 10 it's automatically there in earlier versions you need to add the analysis toolpak tools add-in if I can even remember back that far now I know many people still use 2003 but that was a long time ago ran between there's just a bottom and a top you say 1 comma 5 well how many names 1 2 3 4 5 it'll randomly select between 1 and 5 f9 and it will randomly select so guess what that's going to be the relative position of the item in the list so index array men array is one way so comma no matter whether it's column or row row number will just take the relative position so and then ff9 is evaluate so I'm randomly selecting the name our next example will go over to 16 and 17 this is a formula that can look up the first non blank but we're going to see an interesting thing about index we're going to have to do an array formula here and index can actually handle arrays without control shift enter so down here that argument there says array just like some products as array and lookup or actually lookup is doesn't fit that same it's just pro it's a function from way back that actually can handle arrays those are three function that handles array but we're going to create an array format and we're going to use index cleverly to avoid ctrl shift enter alright let's start up here get first non-blank well I'm going to use index and it's always going to be this now this formula right here this kind of situation might be for a stock analyst right you're entering and there's let's say there were days up here and you're entering stock values right and so you're always entering your first value in it on a different day or you had sample data with x up here so this is a common situation when you need to look up the first number like so I want one here five here 13 here all right so I'm going to look up index in this range right here the array is that and it's relative we'll copy this down comma and then the this is a one-way array so we just want our relative position well we're going to be clever here we'll use match match again is the hole that's a not a built-in function sorry about that match the lookup value well shoot I don't know what to do here well let's just think about down here this one we can see if we could ask the question or in fact let's do it this way I'm going to skip over that for now look up array now I'm going to create an array here and we're going to have to use control shift enter if I could ask the question is anything not blank in this range right here I get a false a true a true a true false false well there's multiple truths right but if we get matched we say hey match lookup true and say exact match it'll look up the relative position of the first one so let's first figure out how to do this we simply highlight that same range and we say is anything in that not empty or not blank the symbols for naught are less than greater than and the symbol for blank is double quote so let's just look at this and hit f9 alright so false true because in the one I know my formula is covering that up but the first item occurs in the second position meaning the the first value we have there so there's our array of trues and falses now this is an array that's a a syntax is a a curly bracket there and commas mean across the columns I'm going to control Z on that because I don't want to hard-code that in that is an array creating a fullness so we're going to have to use control shift enter now I can come over here back to lookup value and just type true that is so cool come over here comma and we definitely need exact match because we want to pick up the first true close parentheses so let's just highlight this and hit the f9 key oh - that's exactly what we want come to here we got our relative position in the row number argument so close parentheses and enter it's going to give us a value error because we didn't this is an array formula this little piece right here requires that we is an array it means that our formula is an array formula which means we have to tell Excel so if we just do our normal thing Enter or ctrl enter that that's Excel thinks it's a regular formula so it goes value error you have to tell Excel I am putting a special formula in the cell this is an array formula you have to hold ctrl shift + Enter now we use control shift enter to tell Excel this was an array formula but look up here the curly brackets are Excel telling you that it understood those curly brackets cannot be typed in fact if you put it in edit mode they go away if you hit enter it's a value error in there are no curly brackets control shift enter curly brackets our a formula works I'm going to drag this down and sure enough it retrieved the last value right no problem now so trick this is a pretty cool trick or 16th trick we can use index and match together an array formula to look up the first non blank now let's copy this actually escaped click here and we'll copy the whole cell ctrl C and paste it right here the 17th trick will just see that index can handle arrays without control shift enter now wait a second I thought we said we had to do control shift enter it all comes down to arguments there's only a few functions that can handle arrays without control shift enter index is one of them but it's only if the thing is in the array argument this is inside of mat and that lookup array can't handle arrays but watch this we're gonna do something really tricky with just this little part right here and index I'm going to say index now now the array right here is sitting in the array argument for index which can handle arrays we want to look some mean indexes or lookup array right but we want all the things so no problem noticed it so one way lookup so I can type a comma and because it's a one-way lookup it's expecting the relative position here but as we saw a couple examples ago if we leave it blank it returns all the items so I simply close parentheses that little thing right there is city sure it's sitting in the lookup array but index will deliver the array to look up a rate no problem as if it was a normal range but this we can do that only because this argument accepts a race of watch enter no curly brackets drag it down and sure enough it works just fine alright our last example wow this is going to be a doozy these are how to use index for even more complicated array formulas our last example for index and match we'll see how to extract records so for example we have a data set here names and scores the max is 99 and there's a count of two so I want to see the students there if there were three it should show three if there was only one it should show one now extracting data you know with filter super easy to do do the filter copy paste same with advanced filter however lots of people like formulas because they update instantly I'm going to control Z Z so any data we put here it just pops out the answer user-friendly for people who are just in raw data right all right now I'm just going to show you one kind of quick example maybe two and then I'm going to direct you some videos if you want to see more advanced formulas because there are a lot of different types of formulas for extracting data that use index and match I'm going to zoom in here and I'm going to highlight this and hit delete so in this situation the first thing we need to do is is ax asked for the Mac so please show me the Mac's and it will tell me which one is the biggest then I need to count those so I'll use count if I'm going to say the range huh here's all the values , what's the criteria well it's the max right and so there's two of them now we're going to build a formula use an index and match to extract the student name so obviously this will be the array and we'll get the row number from or relative position from these numbers somehow matching this against this alright also we're going to need to remember how we the names showed up really what was happening is when we had two names these formulas were being hidden by showing a blank so we're going to start off this formula with equals F and we're going to use our formula incrementer that we saw a few videos a few examples ago and that one however we went from we went seven six five four three two one this one we go want to go one two three four so I'm going to say I'm sitting in C 13 so see dollar sign 13 ko and C 13 I've locked the 13 but not in the first one but not the second one that means it copies down and give me one two three anytime that number is greater than this I'm going to hit the f4 key to lock it then what do I want the value if true is double quote for blank count on the value of false that's our index index the array well that's pretty easy though that's always the values we want to potentially extract I'm going to hit f4 now comma the row number and this example we're going to do here is very common anytime you have duplicates right so we're looking up 99 right but anytime you have duplicates you can't just use vlookup or next you have to do something pretty tricky because what we have row numbers here we have one so the second relative position and the third so when we copy this formula down that row number needs to show first two and then three we can use the small function remember we started with Row two and then three we will give small in this array by the way that you can't put a raise in there that's why sometimes the screen tips are confusing but no problem we're going to put some array in there that is going to give us the row numbers and here's how we're going to do it we're going to say if anything here oops anything here and I'm going to hit f4 is equal to 99 I'm going to hit the f4 key that'll give us a string of trues and falses if you highlight this and hit f9 it gives us a bunch of trues and falses because there's more than one it's an array here control control Z and E oh that's the problem with hitting the f9 key I just did two actions in a row and now I can't undo it so I have to go little four equals this f4 any time we see a logical true there what do I want I want row numbers the value of true is going to be row numbers now watch this row well there's a row function we saw rows with an S but this is row so it'll give us all the rows but right now would give us three four five f4 so I'm going to subtract row I'm going to lock that well that gives us three minus three which is zero so we add one back in that's just a clever robust way of always getting all of the relative positions so f9 one two one two eight control-z now which one's of those do we want control Z well this little series of trues and falses will pick out only that two and the three so I'm going to look at my screen tip right here value of true I don't need the value of false it'll just dump false in so close parentheses and I'm definitely highlighting this and hitting the f9 key to see that beautiful there's our two and three the rest are false as small as we copy it down we'll use the same number incrementer it'll take the two first and then the three ctrl Z I'm going to highlight this comma come to the end here notice it's array comma there's the K control V for our number increment of the first second the smallest second etc close parenthesis on the small that gets us back to row number the small is dumping a row number if I highlight this and hit f9 you can see it's two when it goes down one row it will deliver three that's the row number or the relative position I don't need column number close parenthesis I owe that is the index is the value of false can close parenthesis this is an array formula that little thing right there because we gave it more than one true or false it's an array control shift enter and then I'm going to copy it down and I'm going to test it 99 and there it is alright now that is going vertical I'm going to that's the last form it will build here those will give you more practice on this type of formula down here we're going to see a wild one down here is what we would have to build if we had one of the top three and multiple students here it's just the max right this is a formula right here that will get rid of duplicates and sort the list that's why down here it says Excel magic trick array formula to sort lists and remove dip duplicates that'll teach you how this formula works but that is an awesome formula of course from the mr. excel message board but oops a control shift enter it will dynamically extract only one each of the items and sort them then we had to count them how many 99s how many 85s and then this formula here this formula has to use columns instead of rows because we're copying it direction and if you have 2010 the aggregate function can be used instead of small 15 tells aggregate to do small and aggregate is new in 2010 and it can handle arrays lb it in a slightly different way and not require control shift enter so this formula there's no curly brackets you copy it over and down and if you want to learn about that video right here this link or if you search for this on at YouTube or Google or whatever those videos will come up wow that's a lot about index and match alright see you next trip you

Channel: ExcelIsFun

Views: 706,063

Rating: 4.8604479 out of 5

Keywords: Excel, 2007, 2010, excelisfun, Mike, Gel, Girvin, Highline, Community, College, Slaying, Dragons, INDEX, MATCH, Functions, Left, Two, way, One, lookup, vertical, horizontal, row, column, orientated, differently, approximate, on, table, sorted, Descending, whole, Cell, Reference, Create, Dynamic, Range, more, than, one, different, sheet, CHOOSE, functions, Filp, Table, Get, Amount, in, Next, Bracket, Select, Random, Value, first, non-blank, arrays, Ctrl, Shift, Enter, CSE, complex, array, formulas, extracting, data

Id: bMWv0AN-hSs

Length: 69min 39sec (4179 seconds)

Published: Fri May 13 2011