It’s not just the colon - you can also put a # on the end of an XLOOKUP to give the array anchored on the cell address that you looked up. You can do some pretty cool tricks with that : )
@@cesarmezaroli4069 Here is the test. 1) I had a simple XLOOKUP that was returning a name: =XLOOKUP(D3,$M$3:$M$32,$N$3:$N$32) 2) But then I replaced each name in N3:N32 with an array. I was lazy, so I used =SEQUENCE(1,5,ROW(),3) to generate a one-row by 5-column array in each row of the lookup table. 3) I changed the formula to =XLOOKUP(D3,$M$3:$M$32,$N$3:$N$32)# and now instead of returning one value, it returns the entire array in that row.
@@MrXL It turns out you can put a # on a named range as well (e.g. if TestRange refers to G6, and there's an array anchored on G6, you can use TestRange# to refer to that array). I played around with this stuff a lot in the second half or so of this video: th-cam.com/video/nCLvL7SPAjg/w-d-xo.html
This has solved my YTD issues like a charm! Instead of a fixed second date, it references a TODAY cell for a continuously updating monthly dashboard. Thanks!
So that's a cool trick regarding returning the cell address but why would anyone solve the problem in that way in real life? First, it assumes the data is sorted because if it is not the returned result will not be correct. Why not just use SUMIFS to sum the revenue based on the date being between the start and end date?
You probably wouldn't, but it's a trick that you might want to use elsewhere once you know how, which is what this video does. I would likely use this colon trick (more likely index, but same process) when defining dynamic named ranges for a chart or something. Probably quite rare these days with Power query, but perhaps. The point is that it dynamically returns the start and end of a range and everything in between, which you can then use for whatever sordid request your boss makes.
I am new to pivot tables. Working with Windows version 11. Data has already been dumped into a pivot chart and updates every time it is opened. Can I create a 12-month rolling chart in this table or do I have to start over?
Sure. It was harder back in Excel 2016. But it can be done. The starting date is in C6 =MATCH(C6,H4:H48,0) will tell you where that starting date is in the table. The ending date is in C7. =MATCH(C7,H4:H48,0) will tell you where that ending date is in the table. Now that the match tells you where the start and ending date are, you would use =SUM(INDEX(I4:I48,MATCH(C6,H4:H48,0)):INDEX(I4:I48,MATCH(C7,H4:H48,0))) In case the INDEX function is new to you, it normally would return the dollar amount found at the right row of I4:I48. But in this formula, since it is next to a colon, it will return the cell address of the first month's sales and the last month's sales. The SUM will then sum from that first month to the last month.
I’m pretty sure I specifically remember you mentioning that xloopup returns a cell address when you first told us about the function. Wasn’t it in a whitepaper?
I need to have excel locate the last added cell entry in a column using todays date, and then sum that cell with the previous 11, to give a rolling 12 month total figure.
So presumably without the Sum( ) function the double xlookup should simply give the spilled range alone (It does). Which makes it easier to understand, but is not nearly as impressive!
So many videos, Mr Excel!!!!! Had to watch all 10 videos you posted since yesterday, today lol
It’s not just the colon - you can also put a # on the end of an XLOOKUP to give the array anchored on the cell address that you looked up. You can do some pretty cool tricks with that : )
What does that look like when you use # in this case?
Holy smokes! This is amazing.
@@cesarmezaroli4069 Here is the test.
1) I had a simple XLOOKUP that was returning a name: =XLOOKUP(D3,$M$3:$M$32,$N$3:$N$32)
2) But then I replaced each name in N3:N32 with an array. I was lazy, so I used =SEQUENCE(1,5,ROW(),3) to generate a one-row by 5-column array in each row of the lookup table.
3) I changed the formula to =XLOOKUP(D3,$M$3:$M$32,$N$3:$N$32)# and now instead of returning one value, it returns the entire array in that row.
@@MrXL It turns out you can put a # on a named range as well (e.g. if TestRange refers to G6, and there's an array anchored on G6, you can use TestRange# to refer to that array). I played around with this stuff a lot in the second half or so of this video:
th-cam.com/video/nCLvL7SPAjg/w-d-xo.html
Thanks for the colon magic, Mr Excel!!!!!!
This has solved my YTD issues like a charm! Instead of a fixed second date, it references a TODAY cell for a continuously updating monthly dashboard. Thanks!
Nice use of TODAY!
Àwesome!
Wow! That is awesome...thank you.
The cell reference technique of XLOOKUP is extraordinary.
However, we could simply use the FILTER function or SUMIFS function to do it.
Thank you Mr.Excel for this nice video 📹
this is amazing. thank you sir
It's neat how the evaluator cleanses everything near the colon.
So that's a cool trick regarding returning the cell address but why would anyone solve the problem in that way in real life? First, it assumes the data is sorted because if it is not the returned result will not be correct. Why not just use SUMIFS to sum the revenue based on the date being between the start and end date?
You probably wouldn't, but it's a trick that you might want to use elsewhere once you know how, which is what this video does.
I would likely use this colon trick (more likely index, but same process) when defining dynamic named ranges for a chart or something. Probably quite rare these days with Power query, but perhaps. The point is that it dynamically returns the start and end of a range and everything in between, which you can then use for whatever sordid request your boss makes.
I am new to pivot tables. Working with Windows version 11. Data has already been dumped into a pivot chart and updates every time it is opened. Can I create a 12-month rolling chart in this table or do I have to start over?
Hi Bill, its a neat trick for sure, but why would any1 choose this trick that is not well known over sum(Filter()) functions?
Thank you. Would it be possible to do the same but for excel 2016 version? Im unable to use xlookup
Sure. It was harder back in Excel 2016. But it can be done.
The starting date is in C6
=MATCH(C6,H4:H48,0) will tell you where that starting date is in the table.
The ending date is in C7.
=MATCH(C7,H4:H48,0) will tell you where that ending date is in the table.
Now that the match tells you where the start and ending date are, you would use
=SUM(INDEX(I4:I48,MATCH(C6,H4:H48,0)):INDEX(I4:I48,MATCH(C7,H4:H48,0)))
In case the INDEX function is new to you, it normally would return the dollar amount found at the right row of I4:I48. But in this formula, since it is next to a colon, it will return the cell address of the first month's sales and the last month's sales. The SUM will then sum from that first month to the last month.
I’m pretty sure I specifically remember you mentioning that xloopup returns a cell address when you first told us about the function. Wasn’t it in a whitepaper?
Yes. It was covered in a longer video with 12 different benefits.
I need to have excel locate the last added cell entry in a column using todays date, and then sum that cell with the previous 11, to give a rolling 12 month total figure.
So presumably without the Sum( ) function the double xlookup should simply give the spilled range alone (It does).
Which makes it easier to understand, but is not nearly as impressive!
Ordering my cell references "Animal Style." :)
Far out!