Thanks for this tip from Paul A who was in my Akron seminar.
Paul uses a function like SUM(XLOOKUP:XLOOKUP) to return the total of all numbers from a start date through an end date.
Paul uses a function like SUM(XLOOKUP:XLOOKUP) to return the total of all numbers from a start date through an end date.
Transcript of the video:
It's a crazy secret behavior from, what is it? Nine functions in Excel.
This tip from Paul who was in my Akron seminar last week, Paul I've run into various places.
He won an Excel Master pin out in Albuquerque and an Excel Guru patch.
So he's been around for a while.
Paul's idea here is he wants to get a rolling 12 month forecast and I've changed it so we can put it in any number of months.
So we have two input cells, the 12 there, and then the ending date.
So Paul's date is by month end, right?
So we put in those two input cells and we're going to get the total from the 12 months ending that date.
Now of course the start is pretty simple. It's the EO month.
If it's 12 months, I want to go back 11 months, so start from that date, go back 11.
I'm doing C1 minus one, the 12 minus one to get 11 minus 11, and that gets me the November 30th of last year, 2021 and then the ending date. Well, I put in the EO month zero.
That's really kind of silly.
That would just be in case someone put 10/30 there. Didn't realize there were 31 days in October.
All right, so we're going to use XLOOKUP.
And XLOOKUP doesn't look like it's going to solve this problem because a regular XLOOKUP, we're just looking up 11/30, 2021.
It's going to return the 700,000 right there and then an XLOOKUP from the end, 10/31 22 is going to return that million dollars right there, so you can't do this plus this.
That's just not going to work.
But there are eight, used to be eight, now nine functions over here that have a secret behavior in Excel.
All right, so we're going to start out just real simple here, equal sum of one and 12, and I'm going to take that first XLOOKUP that I did, that's returning 700,000.
I'm going to copy it from the formula bar or in that case from the edit mode and place it in the one. All right.
So we're doing the first XLOOKUP.
We know this is going to return 700,000, and then I'm going to go grab the second XLOOKUP, the one that's returning the million.
I'm going to copy those characters from the formula bar right there.
Now of course this is not going to work either because it's going to sum 700,000 and a million.
We're going to get the same wrong answer that we had before of 1.7 million.
But here's how to trigger the secret.
If we have an XLOOKUP and immediately to the right or left of the XLOOKUP, we put a colon or a space, in this case it's going to be a colon because I want to do everything from the first one to the second one, so I changed that comma to a colon.
This completely changes the behavior of XLOOKUP where XLOOKUP a minute ago was returning 700,000 here and a million here.
It's now going to return as cell address here.
So in this case I14 and in this case I25 and instead of getting 1.7 million, I'm going to get 6.1 million, which is the total.
If I choose all these cells here and look down here, the sum is 6.1 million dollars.
Isn't that wild?
I'm doing an XLOOKUP from a starting point to an ending point and it's given me everything in the middle.
The crazy thing here, we can actually see this work if we go to formulas, evaluate formula.
First, it's going to lookup C6, and it's going to give me the serial number for that date.
Now, right here, this is the key part, the XLOOKUP with the colon right after it, when I evaluate that, instead of giving me 700,000, which is what this identical XLOOKUP is doing, it's not going to do that. It's going to give me a cell address.
Look at that I14 and we'll do the second XLOOKUP. This is the one that's returning a million.
To evaluate I get I25, so everything from I14 to I25, and that's how we get the 6,100,000.
Now that we have it working, we can choose any date here.
Today's date, 2/27 2023, and I want to go 18 months back and thanks to the EO month there it's forwarding to the end of the month and then going back 18 months to 9/30, 2021, and it's getting the answer. What a cool idea.
Now, I've talked about this formula before.
I have an example where you add up everything from fig to orange, and I say it's useful for maybe cost centers in a chart of accounts.
We want to get everything that starts from 2101 to 2199.
Paul's use here is really clever.
Basically doing a rolling 12 or 18 months without having to use the offset function.
The offset function of course, makes everything very slow because it's a volatile function. These are the nine functions.
Choose if, ifs, index, indirect offset switch, and XLOOKUP.
If you put a colon immediately after or immediately before that, instead of returning the number found in the cell, it will return the cell address.
What a great use.
Well, I want to thank Paul for sending that example in and I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
This tip from Paul who was in my Akron seminar last week, Paul I've run into various places.
He won an Excel Master pin out in Albuquerque and an Excel Guru patch.
So he's been around for a while.
Paul's idea here is he wants to get a rolling 12 month forecast and I've changed it so we can put it in any number of months.
So we have two input cells, the 12 there, and then the ending date.
So Paul's date is by month end, right?
So we put in those two input cells and we're going to get the total from the 12 months ending that date.
Now of course the start is pretty simple. It's the EO month.
If it's 12 months, I want to go back 11 months, so start from that date, go back 11.
I'm doing C1 minus one, the 12 minus one to get 11 minus 11, and that gets me the November 30th of last year, 2021 and then the ending date. Well, I put in the EO month zero.
That's really kind of silly.
That would just be in case someone put 10/30 there. Didn't realize there were 31 days in October.
All right, so we're going to use XLOOKUP.
And XLOOKUP doesn't look like it's going to solve this problem because a regular XLOOKUP, we're just looking up 11/30, 2021.
It's going to return the 700,000 right there and then an XLOOKUP from the end, 10/31 22 is going to return that million dollars right there, so you can't do this plus this.
That's just not going to work.
But there are eight, used to be eight, now nine functions over here that have a secret behavior in Excel.
All right, so we're going to start out just real simple here, equal sum of one and 12, and I'm going to take that first XLOOKUP that I did, that's returning 700,000.
I'm going to copy it from the formula bar or in that case from the edit mode and place it in the one. All right.
So we're doing the first XLOOKUP.
We know this is going to return 700,000, and then I'm going to go grab the second XLOOKUP, the one that's returning the million.
I'm going to copy those characters from the formula bar right there.
Now of course this is not going to work either because it's going to sum 700,000 and a million.
We're going to get the same wrong answer that we had before of 1.7 million.
But here's how to trigger the secret.
If we have an XLOOKUP and immediately to the right or left of the XLOOKUP, we put a colon or a space, in this case it's going to be a colon because I want to do everything from the first one to the second one, so I changed that comma to a colon.
This completely changes the behavior of XLOOKUP where XLOOKUP a minute ago was returning 700,000 here and a million here.
It's now going to return as cell address here.
So in this case I14 and in this case I25 and instead of getting 1.7 million, I'm going to get 6.1 million, which is the total.
If I choose all these cells here and look down here, the sum is 6.1 million dollars.
Isn't that wild?
I'm doing an XLOOKUP from a starting point to an ending point and it's given me everything in the middle.
The crazy thing here, we can actually see this work if we go to formulas, evaluate formula.
First, it's going to lookup C6, and it's going to give me the serial number for that date.
Now, right here, this is the key part, the XLOOKUP with the colon right after it, when I evaluate that, instead of giving me 700,000, which is what this identical XLOOKUP is doing, it's not going to do that. It's going to give me a cell address.
Look at that I14 and we'll do the second XLOOKUP. This is the one that's returning a million.
To evaluate I get I25, so everything from I14 to I25, and that's how we get the 6,100,000.
Now that we have it working, we can choose any date here.
Today's date, 2/27 2023, and I want to go 18 months back and thanks to the EO month there it's forwarding to the end of the month and then going back 18 months to 9/30, 2021, and it's getting the answer. What a cool idea.
Now, I've talked about this formula before.
I have an example where you add up everything from fig to orange, and I say it's useful for maybe cost centers in a chart of accounts.
We want to get everything that starts from 2101 to 2199.
Paul's use here is really clever.
Basically doing a rolling 12 or 18 months without having to use the offset function.
The offset function of course, makes everything very slow because it's a volatile function. These are the nine functions.
Choose if, ifs, index, indirect offset switch, and XLOOKUP.
If you put a colon immediately after or immediately before that, instead of returning the number found in the cell, it will return the cell address.
What a great use.
Well, I want to thank Paul for sending that example in and I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
