Bill Jelen: Hey! I’m Bill Jelen from Mr. Excel.com and I’ve got a cool Excel tip for you today.
Mike Gel Girvin: Hey! This is Mike Gel Girvin in Excel is Fun in YouTube and I have a different way to do that.
Bill Jelen: Hey! Alright it’s another dueling Excel podcast I must mean its Friday. I’m Bill Jelen from Mr. Excel. We are joined by Mike Girvin from Excel is Fun at YouTube. Mike has sent me this question that came into him from Hassan at YouTube.
Frankly Mike, I don’t have a clue what you are going to do after I solve this because there is only one way to solve this problem. Hassan wants to know, he puts in a name and a through date. And he wants to add up all the sales through that particular column. I know I colored this up red, I know you’re going to use an offset based on Excel.
Now let’s try and do a couple of things here, which row are we going to look for, well that’s going to be pretty easy. That’s the match function, the match the name here and that set of cells, we’ll press F4, comma zero because we want an exact match. And so Fred is in row 6, easy alright.
Columns, all right, well we could use match again but I’m going to try and do something a little bit more bizarre. I’m going to use equal date value. The date value of this day, May ampersand in quote, space one comma 2009, does not matter what year we use there because I’m going to take that date and then use the month function. The month function of course will return number from one to 12.
Now I know how many rows down and how many columns across and my answer is going to be equal OFFSET. I’m going to start from the corner cell. How many rows down? Well that’s our answer that we get right up here. How many columns over? I’m going to go over one comma and then how many rows tall, just one, how many columns, well that’s where we use this answer right here.
Now that’s going to return in this case, you have 5 or 6 cells, we need to wrap that whole thing in the sum function, and we end up with our answer of 14. So if you will test here, there’s our 14, let’s put someone else, let’s put Joe and December, and our answer here 21. Joe all the way up through December, and 21 it’s working alright. Mike let’s see what you have.
Mike Gel Girvin: Thanks Mr. Excel. Hey! This is a great question. I’ll do a slight variation on the formula that Mr. Excel used. Using SUM and OFFSET, I’m going to use Alt equals for auto sum, and then Offset.
Instead of the starting reference being here, I’m going to start right here, that will help us up later on avoid one argument. So, we’ll start there and then how many rows do we need to go down? I’m going to use that match. I’m going to say look up Sue, within in this whole range right here, comma zero, close parenthesis. That match is saying how many rows down we have to now to deliver 1, 2, 3 comma columns, that’s how many columns over from this position. We don’t need any, because we started here by default, if we leave that out, it will be assumed at zero, that’s perfect. Height is 1, if we leave it out by default it assumes its 1, so I’m going to hit comma and width we use another match.
Let’s say we look up March comma within this range here that date value function formula with the month that Mr. Excel used was just awesome. However, if you don’t have dates you can go ahead and use a second match alright. That will do it, I’ll close parenthesis. We have all the arguments just slightly different than the way Mr. Excel did it, six bucks and if we changed this and test it, sure enough it’s going to work. But don’t believe for a second there’s always more than one way to do it. I’ll show you a different way here, pretty bizarre. It will avoid using the OFFSET though which is a vault to function and some people don’t like vault to function.
What is the index function? Now you have seen probably many videos on one way and two way look up with index. I’m going to just quickly actually copy this little piece right here because we are in use that in a couple of times in this formula right here, equals index. I’m going to look up in this range, and I’m going to see if I can return that five right there using index. So that’s the array, the ordinal position will be that match, and sure enough we get a five. It’s looking up in that range in finding that five.
Now I want to try and find this four. Now this is going to involve doing a two way look up because it is in the middle of the table. So equals index, I’m going to highlight the whole table, comma and we need a row and a column number. I already have the row which is control V that match, comma. And now the column, we will use that match and look up this little piece right here comma within this range right here, comma zero, close parenthesis. So we have our column number and our row number in our index. So Index we are just using this for a two way look up. We got the four and five, now is that bizarre?
If you copy this index right here, control C, actually I’m going to control CC and open up the clip board. Let’s try to being in control CC, escape and then I’m going to copy this. Now why in the world am I doing that? Index is looking up this value here and this value here. Well you can make the Index function look up not the value but the cell reference and that will be volatile. Because right now if you use it this way, it will give us B10 to D10 which will work inside of our sum function. I’m going to click here on Alt equals. I’m going to highlight this range here.
Now notice that the sum function has a cell reference, a colon and a cell reference. If we highlight that first cell reference by double clicking, and click on this. It inserts the index in place of the cell reference, double click here and put that second index. We are putting the index function into the context of being a cell references or colon there, immediately the index goes and I’m not going to return the value, but instead I’m going to return a cell reference, enter. And we can see we got the same value, if we change this to February, we see just we get the same number.
Now let’s run formula evaluator Alt TUF, let’s just evaluate this. You could see when we evaluate it, we got a B10 for that first index and when we evaluate this one down we got a C10. It’s all because of that colon and Index is functioning as retrieving the cell reference instead of the value alright. There’s a couple ways to do that, we will see next trick.
Bill Jelen: That’s amazing! Index in an Index with a colon in between and it gives you the range, unbelievable! There you go, hey, thanks for stopping. Everyone learns something on this one. Point to Mike for that great idea. See you next time for another dueling Excel podcast from Mr. Excel and Excel is Fun.
Transcription by:
Scribe4you Transcription Services