Ctrl + Shift + Enter: Excel Array Formulas 13: Dynamic Ranges: INDEX & OFFSET Functions

welcome to video number 13 for control shift enter mastering Excel or a formulas hey we're in the workbook of a formula DVD books start on the sheet topics hey last couple of videos have been talking about functions that can handle array operations and ones that cannot handle array operations in this video we want to talk about the index and offset functions that can be used to create dynamic ranges now I'm going to click on the link to get to sheet 13 Here I am on sheet 13 and as I've been doing in the last few videos here is a laundry list of the nuts and bolts of things we've been learning about array formulas through the first twelve videos alright let's scroll over here alright the first thing we want to do is define a dynamic range at a dynamic range is simply a range that you can use in a formula chart pivot table or anywhere else a reference can be used that can expand or contract when you add data so you can imagine a data set where you're adding records or where you're deleting and you want your formulas and charts to automatically see when you expand or delete your data set now index and offset functions can be can return a range of cells a type of array but it actually returns a range that can be used in formulas charts and other locations that can expect a reference so that's what we want to look at in this video now I'm mostly going to use show you in index function but we're going to look at some offset examples also now the first thing we want to remind ourselves about index index function is great to look up a range of cells you can either look up a row or a column so we actually saw this calculation back in video 1 and also last video we also saw this and here's how it works index has this array argument and we have entered a range of values and this is a two way range right it has some rows and some columns and the trick for looking up either a row or column in our case we want to look up a column here I wanna with this criteria February I want to look up the entire column but here it is this is a two way lookup so we need a row number and a column number and then index will take the intersection but here's the deal if you put a zero or omit this argument which means leave it blank row number will know to take all the rows and then match for column number is looking at February finding the relative position to so it knows the column omitted or zero please give me all the rows now this index right here if we highlight this actually we could come to the sum and simply click that argument right there watch what happens when I f9 oh that's array syntax now the the thing about this is is the sum function is going to be able to understand it now normally when we have when we create an array in our formula we have to use control shift enter if it's in a function that can't understand arrays like the sum right but check this out I'm going to control Z the first thing to notice is that there are no array calculations in here but when I hit f9 the output from index is not an array it's a range and so there's two things going on here control Z no array calculations f9 and the index spits out a range not an array so that's why the sum function can understand it without control shift enter no array calculations inside our formula indexes output is arranged so sunken can understand it watch this I'm going to control enter I don't see any curly brackets up there and it's doing it perfect it's looking up February and adding it now I'm going to change this to January and sure enough now it's looking up that column and adding I'm going to control Z alright this example here is just to show you that the row number can be either 0 or omitted and we'll know to get all the rows I tend to leave it blank if we are looking up a column it's exactly the reverse meaning that the row number we have to look up use match to determine which row that means Row 2 and then either if you see right here column number it's either omitted or 0 and that will look up the range in this case an entire row and add alright so we want to remember because we're going to see a lot of examples going through in this video about creating dynamic ranges if there's no array operation meaning math comparative joint function argument in the index formula used to create the range control shift enter is not required all right now dynamic ranges there's a great built-in feature we want to talk about excel table feature and if you can use it it's great but there are some drawbacks which then leads us to create our dynamic ranged form is using index or offset function now here's a little table I'm either gonna I'm going to convert this to a table I have field name at the top and records in row I'm gonna go to insert and table or I'm going to simply use the keyboard shortcut ctrl T now notice I just have one cell in the data set selected ctrl T the table has all sorts of amazing advantages it can filter it can sort it could add a total row and the ranges are dynamic so I'm going to click OK now I want to show you how this works we're going to do two things we're going to create a chart and look at the sum function now I'm not going to highlight the range because it will put table nomenclature in which is fine but I want to show you something really cool I'm in C 26 : 2 C 27 now notice I typed out the number 27 alright so an adze here's another example of its ability to do dynamic ranges I want to create the default chart alt f1 that puts the default chart on this sheet FF 11 does the default chart on new sheet all right I'm going to delete everything all I want is just me I want it really small and I'm going to delete that actually I can't resist ID data labels data labels on the outside and so we see these nodes now check this out this is a table so when I click in the last cell in the last record in hit tab it adds a new record when I add six boom that's a dynamic range that means the chart is pointing to this if I was to delete this and then grab this and pull this up its contracted controls easy now it's expanded in both cases the chart and check this out that 27 moved to a 28 so the I better turn my speak on center cells off so table the excel table feature is great but there are some drawbacks sometimes if you're expanding and contracting a lot especially when you get down to 0 records and it can get it difficult some formulas don't work in tables custom view subtotals some of those features don't work so sometimes people don't want to use the table feature alright let's scroll down here and look at an alternative to the table feature and in this example we're going to see how to use the index function to create a define named dynamic range formula for text data now that we can have different types of data you can see we have some text data here number data you can have mix data also and so in this first example to see for examples for text data and then we'll consider numbers and mix data all right so here's a data set now I'm going to deal with a very small data set and I'm only going to highlight in my formula just a few rows past where my records are when you are creating dynamic range formulas if the maximum number of records you're ever going to have is 50,000 you need to highlight past 50,000 rows to accommodate the maximum potential number of records all right so text how do we create a dynamic range Foreman now I'm going to create my formula in the cell here and then once I get it done I'm going to copy and paste it in the to the defined names dialog box it's just a little bit easier sometimes to work inside of the cell than those dialog boxes all right so we're gonna do a text data here now notice we have three records there's a couple situations you could get yourself into usually you're just entering data like this right occasionally you run into a situation where you might have an empty record which in most cases will not work you know whether it's a pivot table or something like that but we'll see both cases alright let's consider the index function now let's remind yourself about the index here is the array argument I'm just going to highlight this range and again I'm highlighting just a few records in just a few rows past the maximum I'm going to assume the maximum number of records I'll ever have is three extra records I'm going to hit the f4 key now what if I wanted to just look up the last item here well one way to do that is to notice I have text and use for the row number that count ah function and so I highlight the same range in f4 count accounts not empty cells it will deliver a three and it will deliver an item so the index function is doing its job it's looking through here it's retrieving row three but we want to see how to use the in depth index function not to retrieve the content of the cells but the actual cell reference B 62 how do you do that well right now the index is in the context of a regular formula we need to put it into the context of a range of cells and here's how you do that I'm going to consider the very first cell that I could ever have in this range B 60 that says B 60 and then I'm going to type a colon now puts that be 60 in there and I'm going to backspace now you have the index function in the context of a cell reference now I'm going to lock this one with the f4 but there it is cell reference colon index now that index is in the context of a cell reference it won't look up the content it will look up the cell reference now let's see this two ways I'm going to highlight it and hit the f9 key now notice it's showing me all the values remember control Z this formula doesn't have any array calculations in it no array operations and it's the index so it's delivering a range of cells notice f9 we see that array syntax but don't get fooled alright now if I hit enter it'll show me implicit intersects and we talked about that in earlier videos if you want to see the first item in the cell use control shift enter now I did that control shift enter I want to use formula evaluator to prove the index really did look up the cell reference be 62 so I'm going to use the keyboard alt mV go up to the formulas ribbon is the long way to find formula evaluator and now I can either hit evaluate or enter counter will be evaluated now watch index be 62 that is so cool index is now looking up a cell reference notice B 60 is the first one and since that's always going to be our start position there you go all right I'm going to close this here that's one way to do it but let's check out the problem with that if I had some text right here Delta will be delivering 4 and it will do it will look up that 0 in that empty cell f9 there's not really a 0 though that's just how the formula treats it notice that's not what we want so if you were ever going to have some potential empties we need a different method I'm going to click escape actually I want to steal this because the remaining three examples are going to have the same exact first part f2 put in edit mode ctrl V now row number well we've already seen a few times in this video series how to look up the last item in a range the trick is you use a lookup function with approximate match and the lookup value has to be bigger than anything in the column so I'm going to use match and the lookup value here there's a few way if use ways ways we could do this notice it's text data but check this out I learned this trick from hami 72 at YouTube we can use the last letter in the Greek alphabet just a single letter and it will always get to the N if we're especially for use in English words now there's a keyboard to get omega the last letter in the Greek alphabet you have to hold the Alt key down and then on the number pad in succession do two three four so I'm an O two three four and then in double quote that is going to be my lookup value remember that's going to be bigger than anything we will ever put in this column so with approximate match it'll get the last this is the lookup big that thing concept and we'll see it with numbers and with text comma and then the lookup range is that same range f4 the default is approximate so I don't need to put anything there close parentheses close parentheses if I highlight this and hit the f9 key to evaluate it you could see sure enough it got this little extra bit here ctrl Z ctrl shift enter now that's one way to do it another great way to do it is same thing we're going to use match but our big value our big text value for lookup value is going to be lots of Z's and we're going to use the repeat function I don't want to type out a lot of Z's so I'm going to use the repeat function which allows me to repeat something so I'm going to put Z in double quotes and guess what I'm going to repeat it 255 times let's highlight this group f9 so that is a big text item it's going to be bigger than anything in this College now why 255 because the lookup argument and lookup value here the maximum number of characters it can handle is 255 now I learned this cool trick from Aladdin at the mr. Excel message board Aladdin them formula master absolutely awesome so that's the big thing concept in this case it's big text all right and then comma the lookup array is here F for closed parenthesis because we're using the default close parenthesis I highlight this group f9 and I see we got everything control whoops oh I did the cardinal sin when you're using the f9 key and control Z control Z only works one time in a formula edit mode so I can't control Z and get back I did two operations so I'm only allowed to go back when I'll have to fix that alright so there you go control shift enter all right still another way to do this instead of looking up using the big text concept I'm going to use this small text concept and the way this works is the smallest text item we could have is in for lookup value is a null text string so I'm gonna put double quote double quote comma within this range right here and now we can't use the default we're going to use minus 1 that's this not often used a third option here and what it will do is now it's going to look for the smallest value in the column so when we give it something smaller that won't then will ever be in this column it'll just keep going keep going until it finds the last one all right so that's and I saw this somewhere online in the last year or so you know and so there's another option highlight f9 there you go control Z control shift enter now when you time these all three of these are about the same all three is about the same and this one is a little bit longer now these are four examples here we're going to see about nine examples of these formulas and then we'll go ahead and time them all right now let's go ahead I'm going to pick this one right here and put it up in define names now you could just use this right in any formula as a dynamic range I copy that control seat escape I'm going to good use the keyboard shortcut control f3 name manager's up in the formulas ribbon I'm going to say new text column or something like that then I'm going to highlight and control V so I can see that there and then click OK click oh here's a great trick once you've clicked ok you can come down here to this collapse button and click it and see if it's getting the right range the dancing ants will tell you if it's the correct range or not alright I'm going to click close and now let's see if we can use it in our foil I don't know I could use some formula or some whatever I'm doing I'm just going to count ah I've already done that within this but I want to count how many things are in this dynamic expanding and contracting range so I'm going to hey I forgot my name oh there's a keyboard shortcut to allow you to paste the name f3 and I have my big text big text so I'm going to double click that and enter and so there it is if I delete this it automatically knows if I put something in both of these it knows to get 5 and by the way you can just use the dynamic range right in your formula so there it would be I mean this would be a ridiculous formula here but I'm just showing you how you could use this and notice I didn't hit control shift enter there's no curly brackets up there alright and so that the two things about this even though we highlight it and hit the 9 and see that array syntax there's no array calculation anywhere in this and the index output is a range of cells I promise you later we'll see some great applications for these defined names or just kind of learning the nuts and bolts here alright let's go down and look at our next example we got to look at how to do number data all right so we want a dynamic range for this column here so I'm going to put equals that first cell reference colon and index the range highlight as far past as you need f4 comma and same match and the lookup value the lookup value we're going to choose is the biggest number possible now you can put any big number here for lookup value that's going to always be bigger than anything in the column but here's the biggest number that except this is an approximation within a couple points here they're of the biggest number Excel can see there's a 9 point and then 14 times 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Excel sees up to 15 significant digits and then scientific notation e + 3 0 7 big num I learned that one from Aladdin at the mr. excel message board he always says hey might as well if you're using big numbers put the biggest number that Excel knows and then you're never going to get in trouble alright so I'm going to highlight that same range f4 the default is approximate match close parentheses close parentheses if I highlight this is a book so I forgot to lock this one if you're going to paste it up into the defined names dialog box that's very important f9 and there we go ctrl Z ctrl shift enter if I were to put something here now it will see it f9 control-z escape alright and now what what are we going to do if we put we have mixed data well we actually seen this formula before I'm actually going to just copy this from my notes here and an nmo control V now we've seen we saw this when we looked up the last city and we had one other lookup last example in earlier videos but we created an array and since it's mixed data I just say hey give me a true or false for when it's not empty notice there's duplicates here if I converted this trues and falses like with multiplying or double negative the false would be a zero and that would not that wouldn't work because we're looking up the big number here and that zero would be picked up as a number so it'd keep going till the end so that's why we divide by zero so we're converting the truths and falses with any math operation we chose division f9 because now the divide by zero flatout will not is not a number so the two won't get affected by now the two has a chance to find that one why - because that is a big number right we're never going to get anything but ones because those were truths control-z now that mass will find the position and so the row number f9 and there is the four position one two three four now check this out this is an array calculation and it's sitting in an argument lookup array argument of match which cannot handle rate count array calculations without control shift enter so if you were actually going to scoop this out and use it in a formula instead of doing defined names you'd have to use control shift enter um maybe I'll show you that and just write top I'm going to control shift enter just show the first value if I were going to count count uh and equals count uh control V and I'm showing these silly examples here but if I hit enter it just gives me some implicit intersection but if I control shift enter now it has a chance to work it's counting how many different bits of data there again that's a silly example right because we have a great example coming up for how to use these but we're just learning the nuts and bolts alright and if you were to put that up into a define name guess what define names can handle array formulas with a rate calculations regardless of their control shift enter or not alright now that is a way to do mix data now we want to come over here and talk about tables because often times four will have a vlookup example later where we have a table or pivot tables you need to figure out how to do a whole entire table now I'm not going to type these values out but here's our data set we have text number number but I want the whole table say for vlookup because our next example will be vlookup one way to do it is to to highlight the upper left corner and then look up the last number in this column that way that colon right there because index is looking up the last one here it'll deliver d-104 so if I alt MV for formula evaluator and enter enter you can see that sure enough index is delivering d-104 and we hard-coded the upper left B 101 there now what if you had something like this by the way that's number formatting there you had some rogue data right in the middle well of course this one's not going to pick it up because we're looking for the last one in this column it just flat out is not going to work right doesn't pick that up so if you wanted to do that and pick it up you'd have to do something like this so I've I'm still in the row number argument but I had to build an array calculation and I wanted to find the max possible row that has data in this range so I created an array of trues and falses that says hey is anything in there not empty if I hit f9 you could see it gives me erase in-text comma means column column column column then go down to the next row comma comma comma then go down to the next row notice we have a little rogue true way over here representing that to control Z so now we simply saw this little formula element earlier because we need row numbers here so if I hit f9 this is our way of creating one through seven so even though this is a rectangular range and this is a just a single column with row row row row row control Z when I highlight this inside the max and hit oh I could do this little trick again Boop and hit the f9 you can see we get all of these row numbers but that one rogue little six and here control Z we use multiplying f9 right so these zeros are not going to affect a max calculation we've seen a few other times in this video series where if we're looking for the smallest number then we can't use multiplying because the 0 will mess everything up but no problem the max inside of row number f9 that will give us our six and index will know to go down this column to the six position so it will be taken the entire table control Z if I highlight this and hit the f9 you know those extra little blank or so these are empty an empty row and some empty cells for in a lot of situations that might cause trouble but in some situations you you might want it so that's how you'd create a dynamic range even if there's rogue data now here we did an array calculation so we if we're going to use it in a form we have to use control shift enter or you can just paste it into the define name's dialog box that thing doesn't care about whether it's an array formula or not it will understand it I'm in a control shift enter if you were going to use it in a formula you might want to consider this the aggregate is a 2010 function and we've we saw earlier how to use the number 15 to tell aggregate to do a small calculation well 14 tells aggregate to do a large calculation and then you simply in the array you do that same calculation f9 and it will pick out because the 14 means 14 means find the large and the one will say find the number one large and it will find that six and aggregate can do the array array calculations without control shift enter control Z all right now that's regular data set with row data right now let's do our example we want to see how to do vlookup and data validation including if we start adding extra records so I'm going to go ahead I've already created the formula here and I'm going to look in the first column so I'm going to use this formula here with repeat table Z a bunch of times ctrl C this is going to be for my data validation drop-down list so it's dynamic as I add new products the list will expand control f3 I'm going to say new and create data validation list so I just call it that and then paste it down here delete and control V now I we did define names once already in this video but I want to point something out I forgot to point out earlier notice that we not only locked all of these cell references but that you don't see any sheet references there right I'm going to click OK now you can see some sheet references here if I click Edit sheet reference put in okay so it automatically puts those sheet references in so this Nate this define name dynamic range is available everywhere I'm going to click close now I'm going to do the same thing for the vlookup I use the upper corner and I search for the last number in this so that will work for the table ctrl C escape control f3 for define names I'm going to say new I'm going to call this V lookup table delete or I hit backspace ctrl V there are the locked cell references but when I click OK they turn to sheet references I'm going to check this table right here Boop looks like it's working I'm going to undo that now I'm going to check this one here is it getting it right it sure is the dancing ants tell you if you got it right or not close all right now let's do data validation list alt DL I don't want to allow anything I want to hit tab and l4 list tab I don't remember my name so I'm going to hit f3 and it's data validation list so I double click it and there you go now I'm going to check this out there it is Wow alright now I'm going to do equals vlookup the lookup value is going to be whatever I selected from the data validation comma f3 to get my name oh yeah it's V look V lookup table it's fine to just type those out right as soon as you get your drop-down there's a dog tag and you can just double click it comma that's in the third the cost is one I want to return I'm definitely doing exact match because I did the first column is not sorted and there we go now let's go ahead and add a new record and this is three inches in point 85 and see I haven't format it the whole column here watch here's a dangerous trick I'm going to copy this all the way down point to the smart tag and say fill formatting only alright now let's go drumroll did my data validation list yes it did it has my new product and his vlookup pulling the right price oh I have the wrong format there I'm going to use the keyboard shortcut ctrl shift 4 for currency so there we go expanding contracting if I were to delete this hit the Delete key now the lip while the list has just totally changed and there it's looking it up properly alright let's look at a couple other things you might encounter what if you had a formula deliver a null text ring delivered by formula or an extra space well the rule of thumb is for data sets is that you really don't want to put extra stuff or tight below your expandable contractive contractible data set so if you're setting something like this up you probably want to have a rule that you don't put data down here but but what if you did well you could add this is our same formula we did earlier with Max and an array calculation non empty in rows the only thing is we added the trim function and the trim function removes all spaces except for single spaces between words you could see count ah is getting 6 1 2 3 4 5 6 that formula delivered null text string if you use the length function to figure out how Eliane function to figure out how many characters are all it'll tell you 0 but there is something there and count out because these cells are not empty count them all right we want to talk about offset offset is a great function along with index now here's some notes here but I'm just going to jump in and look at the offset offset you start by giving it a reference now I'm going to click in the top cell that I want here an F for that first argument says where to start now it can be a single cell or it could be a range of cells , the four remaining arguments define the size and shape based on this starting point now rose that means how many rows down or up do you want to move from the starting point so since we don't want to move in it you either put zero or omitted columns means from the starting point do you want to move left or right any columns if you don't you put zero or a minute now if you want to move columns left or right notice I'm in B to go back one I'd have to put a minus one to put forward one I'd have to put a plus one or plus 2 or whatever it's the same with the rows adding rows is a positive number subtracting rows is a minus number these first two arguments rows and columns it means how far do you want to move from that starting position once those two things have determined the starting position then you have to give it the height well I'm going to use the count function because I'm never going to have any empty records and I'm going to only have numbers so right now count will deliver one two three four five and that will be the height I'm going to hit f4 comma now width you could comma and say 1 or you can assume the default and the default is always based on the reference either the height or the width which I didn't put in here if you leave those out it will assume whatever the width and height this of the reference that's that starting position right there reference all right so since that reference is one column wide the width I'm going to leave it off and now you can see if I highlight this in hit the f9 key there it is control Z and it delivers a reference also if I hit enter it's given me implicit intersection you could use this in to find names also all right now actually let's go ahead and use this and I'm going to show you how to create a chart chart is one of these great things that people like to have dynamic right so we want our chart let's go ahead and take that offset I copied it made sure everything is locked ctrl f3 and I'm going to say new and call this chart data no spaces with your name up there highlight this backspace control V click OK I'm going to test it oh there we go absolutely beautiful you can see the sheet references are put in click close now I'm going to just highlight this range here and create the default chart control f1 do a little cleanup here might as well add a one way to do it is to go up and notice if you have the columns highlighted you can use the series function up in the formula bar and replace but that gets a little tricky and sometimes it's harder to do there but might as well just go to the design and select data actually this button is the trick to all chart e select data because you can edit add and delete any ranges and build your charts just the way you want here's their static range here I'm going to click Edit and here's the trick you want to come to series values you have to leave the sheet name in and the syntax the little apostrophes in the explanation and highlight only the range then delete it or hit backspace and put your name I hit f3 key and I'm going to take my chart data now notice look at this there's sheet reference and the define name when I click OK that is going to change if I come back and look at it in edit mode and click this collapse button you can see now it has the name of the workbook and then x18 dot XLS M exclamation point and then the defined name that is what's supposed to happen I'm going to click the uncollapse click OK click OK okay let's try this I'm going to come here and type 189 and just like that my chart is expanding if I were to delete this it contracts absolutely beautiful my dynamic range using offset and define names and now I know how to use it put it into charts it's all about that select data all right let's scroll down here a couple more examples now we want to see here if I select a city SF or Tacoma I want a dynamic drop-down so if I select Tacoma I want to see a drop down over in this cell showing just the names the customers for Tacoma if I change this to SF I want to see just the names here in a drop-down alright I'm going to show you two different ways this will get a little bit tricky right here we use offset and index all right offset well where are we always going to start the reference well I want customer names I'm actually going to click on the field name just because part of our formula is going to move down once it finds the starting position for our city so that's going to be my starting position I'm always going to come up move down a certain number of rows now we got to think if it's SF how far do I need to move down from that blue reference one right there's SF if I changed it to Tacoma I would have to go down one two three four well I can use the match function with exact match there are duplicates here so if I use exact match and look up the city name it'll always give me the relative position which is the first time it sees it in the list so four rows match will work just fine I'm looking up this f4 comma within this range right here f-for comma zero it's got to be exact match all right so that's the rose again if it's Tacoma it'll start from this blue and go 1 2 3 4 because Tacoma is the fourth item in that list right there now columns columns I don't need to move anywhere I don't need to move left or right from that starting position so I'm going to keep the default the height well what is the height going to be 4 SF 1 2 3 what's the height going to be if there's Tacoma here 1 2 so I'm simply going to do a count if based on this criteria in this column here so height count if the range f4 comma and there's the criteria f4 close parentheses all right so there's the height the width I could put a 1 here but I'm going to assume the default which is the width of that reference there 1 if I highlight this here and f9 sure enough for SF it got their control Z control shift enter if I change this to squint Tacoma now if I highlight this in hit f9 no way that is absolutely beautiful all right control shift enter now let's see how to do this with index now with index it's going to be a little bit more complicated formula with more stuff to type out than the offset but what's the big advantage to index index is not a volatile function offset is a volatile function volatile means that anytime Excel recalculates the function will recalculate even if nothing is changed over here and down here under point number 19 there are some things that cause recalculation things like hitting entering any new data insert in a row to leading all's hiding columns all sorts of things we'll call cause recalculation so in big spreadsheets where calculation time is slow it may be wise to not use offset so then even though this formula here will be a little bit more difficult it won't be volatile all right now here's the trick to index what if we're doing Tacoma that means we have to actually look up the cell references see 59 and then have another index that looks up see 160 so let's do the first because we can we can look up the first cell reference in this range by simply using an exact match so if it was Tacoma it would be easy to get that first cell reference see 59 so I'm going to say equals index and the array that's the range of values the cell references we potentially want to look up F for comma and the row number well I simply do match I'm looking up Tacoma f4 within this range right here f4 and I'm going to do exact match because I want to find the first one right so if I ctrl enter you can see it's getting GG well it's not in the context of the cell reference now we're going to have to just change this a little bit to get the second one but how do we tell index to look up a cell reference : and then control V the only difference is think about for Tacoma if right now with this exact match it's looking at the fourth one well what if I subtracted the total count for I'm sorry added the total count for Tacoma it would go one two and then subtracted one it would work perfect notice for San Francisco if this was San Francisco I'd get this as the starting position but if I counted San Francisco would be three so I could go 1 2 3 and then subtract 1 and it would give me the ending cell so we simply have to and I'll even get rid of that there it is the row number it's match right now if I highlight this in hit f9 it's 4 but this is the second index and I needed to go down one more control Z so I add count if all of the cities f4 comma the criteria f4 that's always going to give me one too many so I subtract one there for Rome number f9 that gives me 1 2 3 4 & 5 index will know to get the cell reference c-160 control Z now I'm going to close parentheses highlight it f9 you got to be kidding me ctrl Z ctrl shift enter if I change this to 2 SF if I highlight this and f9 absolutely beautiful control shift enter alright so for our dynamic drop-down I'm going to use this one right here control make sure I have all the absolute cell references ctrl C and edit mode ctrl F 3 new this is going to be I just type dynamic data validation I'll highlight down here backspace control V wow that's a big one click OK I'm going to check it right here no way that is so amazing click close all right now I come over here well DL tab L tab f3 I think it's dynamic data validation right and then click OK so right now it says SF oh that is so cool joepie what if I change it to swim now that dropdown changes to Willie and Dana absolutely awesome all right so index are offset here's a little table indexes not volatile offset is easy to use well it kind of depends on your point of view I tend to stick tend to stick with index but offsets pretty cool too recalculation time those are all the things that's straight from Microsoft helped many of the things that can cause recalculation all right let's go to our next example all right let's scroll down we got two last examples Oh number 20s a great trick here sometimes you want to add the last five or look up the last five for whatever calculation or the last twenty or thirty or however it is you have this expandable list and you're only interested in the last so many so we can build a dynamic range for the last so many we're going to do five right we have a small data set here the only trick is do you want to include empty cells or not so the first example sometimes you do sometimes you just want the last five and if there was an empty right maybe there weren't any sold on that day so here's the format and we want to add in this case here's the formula for that the trick is we're going to do index colon index so what we're doing is we're looking up two cell references for this first example I need a range that's dynamic from be 189 to be 193 well the 193 part of it the second cell reference in our range is easy we just do match with a big number and it will find that position it's finding this one that's a little bit tree well it's not that tricky we'll just do the same thing we'll find the position of the last one and subtract 5 actually we won't subtract 5 but here is the second index and notice I took the match to get the position we use the big number concept in there so of course it's going to find that cell reference b1 93 4 before the colon that means that cell reference right there 89 I took the match oh yeah I just find the last one and then subtract the 5 right there because that's one too many we add one back in so that will work and it's beautiful it will always get the last 5 even if I start typing numbers here right so there it is that some function is automatically updating as they add or delete records all right so how are we going to change this to exclude that well let's look at this data set what if we asked a question like are anything is any data in here a number we get false false true true false true true true well we could take that array of trues and falses and multiply it by an array of numbers 1 2 3 4 5 6 7 if we multiply then the 5 here would be times the false because that cell is not a number and so we'd get an array of relative position 7 6 4 3 2 then from that array we ask the question which one is the fifth biggest and that's how we're going to get cell b88 again finding the last one here is easy we do match with the big num so I'm actually going to copy this and put it over here and so we have this : there's the index that one's going to work just fine because it's looking up OOP that last be 193 it's only the first index for the first cell reference that row number argument so I'm going to highlight that in hit delete all right so I want the large and then I want an array of relative position so in that array I'm going to say if and then is number the is number can look through here it delivers trues and falses I'm going to hit f4 right so if I logical test that and click that and f9 it to evaluate I can see true false true and so there's that false that's going to help us skip over the fifth empty cell control-z now I say if is number and then I come to the end of the logical test right there comma then I want we have to create our formula element that creates an array of relative position so row of all of them f4 - row of the first one 4+1 if we highlight this and hit the f9 key you can see there it gives me that's all of them but the is number will only give us the ones that are have numbers so now I'm going to very carefully come to the end here and close parentheses now I need to put a K actually let's do this Boop and hit the f9 on the if and sure enough you can see there's our array only of the row numbers that have numbers in them and now we can ask the question give me the fifth biggest it'll be the second one right there ctrl Z I didn't get that right it and click these screen tips for these big formulas are just absolutely mandatory comma and then the K well I want right there's a K we want the fifth largest and then f4 close parenthesis and now I'm going to click on this row number here and hit the f9 key and there it is that's how this index is going to look up the cell reference b88 cuz it's the second cell reference in that range there control Z now this one is going to require control shift enter because there's an array calculation here anytime yeah we have an array calculation we've seen the if before the if will trump any any other function that may not require control shift enter for and rate calculation this Trump's it so this is an array calculation so we have to use control shift enter if I just hit enter it's not going to work control shift enter and there we go this requires control shift enter in the cells but not up in the defined names dialog box so defined names dialog box understands array formulas or non array formulas it's all the same to them all right let's scroll down and look at our last example alright here we have a column and we need a formula as we copy it down that will always count how many grades are greater than 2.5 but only for the quarter so we want a one formula we can copy down here's how you do it longhand right let's see town if that range right there greater than or equal to the 2.5 when I get here I somehow need the range to change down here I needed to change yet again now doing it manually if you had thousands of rows would be torture right so that's where a dynamic range that changes like this would come in handy then we have one formula to copy down now the trick here is noticing that there's a pattern here text so when I get down to this cell right here we can define this range by two endpoints because we're going to use the index we're going to need this first cell c99 and the last cell C 202 the C 202 will be easy that'll be a relative cell reference it's only going to be that one how in the world are we going to look that up but notice the pattern is that there's text right so the last bit of text in this column over here is that when somehow I switch down to here the last little bit of text if I had an expandable range as I copy down would be there so that's going to be our marker to determine where the start point in our range is all right let's look at an index formula here I want to start off by seeing if I could look up the first grade so if I copy this formula down at least it'll be looking up the first grade for each quarter so the trick is a one of the tricks here is to highlight two cells lock the first one with the F four that makes it expandable range that's luck this is not as we copy down it will expand and now I want to look up this number here but when I copy down here it should be looking up to point one so for the row number I'm going to use match and I'm going to use the big text concept we learned earlier double quote and I'm going to use Greek Omega so alt and then on the keyboard pad two three four in double quote and I'm going to look up that's big text I'm going to look it up within that range and this is going to be the same expandable range now with Big Tex or big number it's always approximate I'm going to close parenthesis now let's control enter and just notice what this does it's going to get me three point eight all the way down now notice how these ranges are expanding right but when I get down here what is matched to what position for this blue range is match determining that one right there because now the last text item in this column is that when - 2012 all right so now we know index if it's not in the context of cell references looks just look stuff up now let's put it in the context of the cell reference : relative cell reference now this is going to create an array of values I'm going to ctrl shift enter and drag it down but we can use formula evaluator to check this out highlight are just the f9 trick f9 sure enough look at that it's got all of those values escaped let's do it here f9 sure enough it's got those values if I am all Tempe and run formula evaluator the match has got to the index gets there it is C 199 to see 202 absolutely beautiful now we can use this now notice are there any array calculations here ah does index deliver a range yes it does even though we see it when we f9 oops we see it when we f9 here as a enter where you see array syntax we know that this really isn't an array so we're going to put this into the count if function range now we already saw out a whole video on showing examples of the fact that the range argument cannot handle a race but guess what since index is spitting out a reference and there's no array calculations here it'll work perfectly so then the criteria is greater than in double quotes and person f99 f4 and you've got to be kidding me control-enter no control shift enter I don't see any double curly brackets there double click and send it down now all we have to do is add a little bit there's our three that's the count of grades greater than or equal to 2.5 here we get a proper count of only 1 now we can amend this if and I'm going to look one cell over and one cell down that way when I get to right here it's looking at winter and I'm going to say is that not empty if it's not empty then the value of true is that count if otherwise the value if false is going to be no text string close parentheses control enter double click and send it down Wow all right so that's our last example for this video we saw a lot about index and offsetting and about creating dynamic ranges we talked a lot about big numb and big texts talk about how index and offset create these amazing ranges and near the end of the video we saw some great amazing uses for dynamic ranges all right next videos video 14 we'll talk about boolean logic we'll see you next video

Channel: ExcelIsFun

Views: 50,737

Rating: 4.9142857 out of 5

Keywords: Excel Array Formulas, Array Formulas, Microsoft Excel Array Formulas, excelisfun Array Formulas, How to use Array Formulas, Advanced Formulas, Mike Girvin, Mike Girvin Array Formulas, Mike excelisfun Girvin Array Formulas, Ctrl + Shift + Enter, not just enter, Ctrl Shift Enter DVD, Ctrl Shift Enter Book, Dynamic ranges, OFFSET function, INDEX function, Defined Name Dynamic Ranges, SUMIF, COUNTIF, Big Num, Get Last, Big Text, Big Number, Approximate Match, MATCH function

Id: 5ko5ES8ZLuY

Length: 60min 22sec (3622 seconds)

Published: Thu Jan 23 2014