Excel Magic Trick 1071: Dynamic Range Based on Product and Start & End Month: OFFSET, INDEX or AF?

welcome to Excel magic trick number 1071 hey if you want to download this workbook file and follow along click on the link below the video hey in this video we have a great example we need to look up product to and get all the values from February to April so right now we need to look these values up and add them if I change this to product 3 boom I need a different set of numbers if I change April to August boom I need a completely different set of numbers controls easy ah so how could we solve this there's actually a bunch of ways we could solve this I'm going to look a bunch of cool tricks the first couple examples we're going to create dynamic ranges using offset and index which are probably the best way to solve this then the third example we're going to see what in the world you do if you have the column numbers you need it in a cell like this and then we have a few extra why hold examples here now actually I have some notes over here about the concepts that we will see in these examples here and the page numbers in the control shift enter book that I wrote that's a book about advanced phone as an array for this by the way it's not just about array formulas there's lots of cool advanced stuff in there like offset and index I'm going to move that over because these form is going to get pretty big all right we're going to start with offset in the cell to create this dynamic range now here's how offset works offset we're going to tell offset to start at cell a 14 go down to Rose then over to columns and then we'll say hey offset the size of this range is one row by three columns all right we're going to start up here equals offset all right so the reference is the starting position so I'm going to click on cell a 14 comma Rose we're going to use match to lookup product two comma within this range of values match finds the relative position so one two it will report to two offset that's how many rows we need to go down comma we're using a zero here these are sorted right now but product might not so I'm putting exact match lookup comma now columns so far we've started here and gone too but we need to move over to so four columns we're simply going to look up using match February comma within this lookup array right now I'll say where's February the second position comma those are not sorted so we have to say exact match all right so now we're starting here going down two rows over two columns boom that's the starting position now offset knows where to start comma how high or what's the height of this range hey the reference is a one by one meaning a single cell so if we leave this argument empty it assumes the height of the original reference which is one so that's good for us comma the width we need one two three so watch this we're going to say we're actually going to have to use to match functions we're first going to look up April and subtract February to get three but watch how we look up April lookup value I'm looking up April comma the lookup array if I highlight this it would be one two three four and I want five minus three so I'm going to highlight one cell before we could add one but because we have as part of our lookup table that first column it's perfect comma zero because it's not sorted all right that'll give me five and we can prove this to ourselves by clicking and hitting f9 that's going to give us five control-z after you hit f9 but we want to subtract we've already typed that out ctrl-c and ctrl-v that will give us a two click on the width f9 there's our three and that's dynamic no matter what two months we put in here it'll always give us the right width control-z close parentheses on offset offset has all five arguments entered I highlight in f9 and no way look at that it got the values now check this out if you watch these videos you know this is array syntax array formula spit out an array of values plot offset and index spit out something yes it's an array of values but it's a range that other functions can understand without control shift enter so when we use offset if there are no array operations in our offset causing us to do control shift enter we don't have to put it in some product we just put it in some and hit enter absolutely beautiful we can test this right bring it and there it jumped down here I'm going to highlight this and watch this I'm gonna look at the status parts is some 484 it got it right control Z now offset that's pretty groovy all set is a volatile function which means if you do anything like type something and hit enter in Sirte column it will recalculate the vast majority of time at the time that's perfectly okay but sometimes if you have large spreadsheets that might slow down calculations so we can create dynamic ranges using the index function and index is not going to be volatile now index will create the same range but in a totally different way here's what we're going to have to do index will actually look up the cell reference see 16 the first one and then it will look up the second one e 16 and from those two cell references create a range of eyes all right so index it's a great lookup function where you can do two way lookup here's all the values now notice it's got coordinates for the starting position one or out of the row is 1 2 and the column is 1 2 so 2 2 we put in our whole two-way array into the array argument comma now I need a row number well I'm going to use match to look up product just as we did earlier that will report a 2 into the row number comma and then the coordinates for column match and we're looking up February , within group come on zero close parentheses that will return we'll watch this index if we highlight in hit f9 it does not return a cell reference it returns the content of the cell 135 control Z that is how index is programmed to work however if you put index into the context of a cell reference or a range of values it will look up the cell references so watch this : right cell ranges go from one cell colon to another cell so I got that colon there I'm going to copy this because if we're looking up the second cell reference this works exactly the same except for one difference oops I forgot the parenthesis on the end the only difference is we're not looking up February we're going to look up April see April row turn one two three four so the coordinates are two four now if you highlight the index again alone f9 it returns the value control Z but if you highlight everything including that magic little colon then f9 is returning a range of values now it's actually looking up the first and second cell reference and then everything in between if you're in edit mode and you hit f9 you can't see the cell references but index really did look up those cell references in fact we will look at an example of how to see that in just a moment index just like offset it's spitting out a range of values we don't need to use some product we just throw it into some and enter now let's run formula evaluator formulas io air is right there or we can use the keyboard alt M V and watch this if we click evaluate or hit enter just watch this beautiful evaluation enter enter enter watch index is about to get calculated boom it got C 16 and then enter enter enter boom about C 16 and a 16 so it's actually looking up cell references absolutely beautiful now our next example what if we were not given February in April and I kind of like this because it's explicit it's visual from February to April but what if you were confined and your solution had to have column numbers in the cells then the formula would be totally different but guess what I'm going to use this one I could use either one of these I'm gonna use offset and then change some of the inputs here I copied it in edit mode now I'm going to put it control V here in edit mode and now where is the information coming for this formula it's not coming from the relative position of February or April it's coming from 3 and 11 oh wait a second it's supposed to be three four five not eleven all right so the information is coming from a text string this is text we need to get the first number which is column three and the last number which is column five so check this out getting the first ones relatively straightforward hey we're just going to say hey please give me the left comma how many characters well I can't just put one because sometimes that might be ten right so I'm going to search for a comma that commas in double quotes within here and search will tell me the position of that comma which is two and we always need to subtract one from it and that will always get me the leftmost number even if it turns out that this is 11 or 10 or something control-z now getting the last one well if we use the left let's use the right now the right of this text string comma the problem here is just like with the left sometimes it'll be a single character and sometimes it'll be two so and I don't want to try and search for the last comma because that little formula element can get complicated so I'm going to give the number of characters to extract from the right argument an array constant one comma two because the biggest could ever be is two for 12 right well this is a function argument array operation so when I give this argument to new values right will spit out two guys f9 to evaluate it notice it's a comma and a five and a text five ctrl Z now watch this if I just enter this and come up here and put an eleven here to check it out now we're going to get f9 two numbers right as text let's put this back to five and what we're going to do here is first off we're going to add zero because I want to convert those two numbers the end when there's a single number f9 when there's a single number we're going to get a value error if that's a double-digit number then the big number we want is at the end but notice it's always going to be the last number in this little mini array control Z so I'm going to use the lookup function lookup which only does approximate match not exact match and if you give the lookup value an approximate match that is bigger than anything in the array it will always get the last one the biggest number it could ever be here is 12 because of our 12 months so I'm going to use a 13 you put whatever big number you want there and that will always look up the last number even if it is 11 control-z now I actually learn this kind of this trick with right from X larium at YouTube and actually have a note over here and this is the formula I learn from them this is a little bit more robust because it'll find the last number in any text string and that's a pretty cool trick here now we have our bits of information starting column ending column we can amend this formula now I'm actually going to copy both of these formula elements ctrl C C to open up my clipboard clipboard is up there if you don't have cc keyboard turned on here's the last little bit for the five copy escape now I'm going to come here alright so we used offset and the call the row number is fine because we use product and jump down to its the columns right that's giving us a 2 and we really want to use this left that gives me 3 so I have to subtract 1 now if I click on the columns and hit f9 boom now it's got that 2 which is how far from this position do you want to jump over control Z finally the width we use 2 bits of information here we need the thigh from our last look up there getting the last value there and we subtract our left now that's giving us three we have to add one because we really want to subtract two so we have to add one back in and boom if I highlight that and f9 there's our 3 for width the information in this formula for the columns is coming from the text string in the cell that is a wild formula now those ones probably the first two are the best that one if you were constrained by this requirement here now another example we could use is we can actually use vlookup to do this but here's the deal what if you are always looking up the same three columns 3 4 5 we could use vlookup and we wouldn't need to worry about a dynamic range I'm going to look up this product within this table right here comma and this arguments expecting a single column number I'm going to give it 3 columns this would be a function argument array operation so in array syntax curly bracket 3 comma 4 comma 5 in curly bracket comma 0 because these are not necessarily sorted now vlookup we give it 3 values here so that instructs vlookup to spit out bone 3 values there's our 3 values now we can put this inside of some product to add now most of the time as I mentioned in my book about our rate constants array constants in formulas like small and large and many other or and other functions if you're using a rate constant to do a function argument array operation you don't have to use control shift enter but vlookup is one of those weird one this column index number that doesn't abide by that now the strange thing about array form is of course is there's no guy Microsoft doesn't put out rules about how all this work so it's just kind of hit and miss when it comes to that but vlookup we got to put in some product some product however that argument can handle this array of that is created by vlookup no problem enter and it adds them right up now if you wanted to have this dynamic depending on what you put here and here I'd use one of these formulas up here but if you wanted to go crazy you could create from these two inputs a variable length array of sequential numbers now the information about where to start an in comes from match so I'm going to take match and I'm going to look through product and all of the months comma 0 and I need some sort of range of value so I'm going to join a colon in double quotes to a match that looks up Apryl comma within put this whole range here comma 0 now if I highlight this and hit f9 it creates O 3 to 5 now notice it's txt but that actually is a row reference it means go all the way from Row 3 to row 5 as text well we need to convert it back to a reference so indirect function does exactly that text that's a reference converts it back to a reference if I highlight this and hit f9 it will not work it says way too many characters because from Row 3 to 5 that's a lot of cell references so we can't see it there you actually can see it in form evaluator but it just is huge that's a 16 thousand times 3 or something but if we put it inside of row row we'll look at that row reference and just spit out the array f9 3 to 5 controls you notice those are semicolons that means row control-z now I can copy this as a formula element oops I'm going to put this back here just to leave it there and come and highlight this little bit ready control V now this oh it looks like I missed that last curly braket I better delete if I hit enter not going to work number vlookup is one of these functions that just doesn't work like a lot of other functions this column index number even though we had an array constants and some product was handling it now some product can't handle it so in this case since we want to be unambiguous I'm putting in some because this formula requires control shift and enter oh that is better to do one of these up here now there's a few other ways we can do this or other situations we might be in here's that same formula I just did and there's our row etc to create that dynamic array of sequential numbers but I put it inside of index because index array argument can handle array calculations without using ctrl shift enter the trick is is that the row number argument right there has to be empty or 0 and then index will return all the values and then boom we can use it in some product without control shift enter that's ridiculous all right here's another ridiculous one if the information came like this and this one is ridiculous because we be limited to three months right which might be fine then we could do some ifs array operation to go and look up three values now what this formula does in the some ranges index is looking up the whole row so match is looking up that product and this index here because again column number is empty it says give me all the columns so we have the row number all the columns f9 that looks up an entire row right control Z well that's sitting in some that's like a row of values control-z so now the criteria range is the month and we do a function argument array operation and criteria looking at February March in April we're looking up February March and April that's three values so that instructs some if to return three values which is exactly one two three or in this case February March April which would be oh yeah that's right f9 and there they are still another way we could do this and this actually has an interesting that I never learned before doing this formula address is a function that you allows you to create a cell reference so what you do is you give it hey a row number and a column number and it creates the cell references text but watch this match is looking up product four so if I hit f9 that's a single number it's the row number 18 right there 18 control Z you can notice I highlighted this whole range here from a all the way down to there but watch it that's a single number but column that's a function argument array operation because I'm giving match three values so if I come here and highlight that boom it's three values so address has a single row number which is 18 and three of these so it's going to have row 18 with column three four and five in address we'll know how to deal with that watch this if i evaluate f9 it returns the cell references the exact cell references here controls the indirect to convert them back to cell references and then n to dereference them and allow them to go into some product to add Wow that was an epic video lots of maybe potentially useless stuff for this particular solution but interesting stuff in it of itself there's some notes over there all right we'll see you next trip

Channel: ExcelIsFun

Views: 27,237

Rating: 4.9014778 out of 5

Keywords: Excel 2013, Excel 2003, Excel 2007, Excel 2010, excelisfun, Mike Girvin, Highline Community College, Ctrl Shift Enter Mastering Excel Array Formulas, Dynamic Range Based on Product and Start & End Month, Dynamic Range Formula, OFFSET function, INDEX function, MATCH function, 3 way lookup, Dynamic Range based on three parameters, Array Formula, Circled Chicken, Xlarium, Lookup last number in a text string, SUMPRODUCT function, Dynamic Variable Array of Sequential Numbers

Id: lh4VjEoD5WY

Length: 21min 58sec (1318 seconds)

Published: Sat Feb 15 2014