Great video!!!Filter is efficient, xlookup is beautiful , offset is visual. There are endless approaches. Other couple of them: =FILTER(B12:E128,ISNUMBER(XMATCH(B12:B128,SEQUENCE(C5-C4+1,,C4)))) =INDIRECT("B"&11+XMATCH(C4,B12:B128)):INDIRECT("e"&11+XMATCH(C5,B12:B128)) =INDEX(B12:B128,MATCH(C4,B12:B128,0)):INDEX(E12:E128,MATCH(C5,B12:B128,0)) Who likes filter likes science, who likes xlookup likes art😊
What I know about excel is thanks to you, Mike. I'm learning so much and using every day in my job to solve many challenges! Each formula has new tricks! Thank you!
Great use of XLOOKUP, Mike!! :-))) From my side almost old way (almost because of SEQUENCE and dynamic arrays) =INDEX(B12:E128,MATCH(SEQUENCE(C5-C4+1,,C4),B12:B128,0),SEQUENCE(,4)) This can be used for both a sorted and unsorted range (but only if we have a year column with unique values). Once again, thanks for a great video, Mike :-))))
It's a nice function. It's a real shame that INDEX didn't ignore FALSE (or Zero, negative or NA) values. As far as I can see, FILTER() is just an INDEX function that allows for that. For example, if I used =INDEX(B12:E28,ROW(B12:B128)*(B12:B128>=C4)*(B12:B128
@@ricos1497 Just a fun note: back in the VERY first edition of Excel, back when it was Multiplan not Excel yet, INDEX was the second lookup function, after LOOKUP, but before VLOOKUP : )
@@excelisfun it makes total sense that it would be, it's such a logical function on a spreadsheet. I'm surprised they even bothered with either lookup and didn't just rely on the match function in the row and column criteria. I suppose it's far more obvious now that there are dynamic arrays that return tables of data.
Thanks Mike...I liked Filter the most but xlookup is up on my list too. Thanks for the video. Good luck with progress on the book. Looking forward to it whenever you finish it...
I prefer FILTER because of its versatility (robustness) and the fact that its filter logic seems more intuitive to me. I just wish we could use wildcards, though, just like we can in the *IFS-functions.
@@excelisfun I know Mike, it’s just that in SUMIFS, for instance, we don’t even have to, we can just use “Soyuz*” as a criterion. Very elegant - no need to ‘stack’ functions (so much).
@@t.pigeon2384 To be absolutely clear, with *IFS-functions, i mean functions like SUMIFS, AVERAGEIFS, etc., not the IFS-function itself. That’s a different kind of function. If those are the kind of functions you were thinking of as well, then... I fail to see exactly how you would use those functions in the filter criteria. Can you give an example?
This is super amazing and XLookUp is just getting crazier day after day... Dope.... I'm happy I got my M365, now I can try all your lessons from day 1 of your M365 lessons Thanks sir... You're super amazing
@@t.pigeon2384 yep, Mike mentioned the sort issue in the video. I'd still use it though, I'd just be aware of its limitations. Although these days, I'm certain I'd have all that data in power query anyway, so that users couldn't break it by sorting!
@@ricos1497 whoops didn't watch the video :) I always try to do the magic tricks without or looking at answers and if my answers match, I skip the video. Sorry Mike!!!
Filter is so neat. I fail to have ready a set of criteria to use in the "include" argument of FILTER. For example you used the AND logical test (B12:B128>=C4)*(B12:B128
Super vid, Mike! I think FILTER works best when using Excel Tables because they are frequently sorted by users in various ways and have columns inserted and deleted. Also. I've been coloring the cell containing a spill formula a certain way - diagonal shading from the top left to bottom right in the cell. It makes it easy to identify the spilling formulas.
Extremely good point. Filter is the only formula that works no matter what basically. I would propose to modify the solution as follows. =SORT(FILTER(B12:E128,(B12:B128>=C4)*(B12:B128
In a case like this (filtering between two sorted limits), Xlookup is the easiest to understand just by looking at it. Filter would be my next choice. With LET, any of these will work even better
This is brilliant but the non 365 users will not be able to use either of these solutions since the offset is using the xmatch. Regardless of the 365 impediment these are great solutions.
Why haven't I seen this one before? , all three methods, wonderful, well perhaps not filter, but using using xlookup to return an array was something I had no idea about. I don't think it's in the MS docs?
But... FILTER is the only one that can handle any type of sort, and you know what we people do with data. Colon operator for looks like INDEX and XLOOKUP have been around forever, but since Microsoft Help is so horrible a lot of the time (like what are the people in the help department at Microsoft even getting paid for), this does not show up in a lot of documentation. But, in this particular situation, the colon operator does show up in the current Microsoft Help for XLOOKUP.
I don't know if this should count because it requires an area the size of the table to spill properly, but here goes... =SORT(IF((B12:B128>=C4)*(B12:B128
Sweet! However, if the lookup table is sorted by year, only XLOOKUP works and displays the full table of values between the Start Year and earlier End Year. Disappointingly FILTER returns #N/A (although a good hint!), and OFFSET returns only the Start Year. FILTER is still best, but is there a way to restrict the Start and End years from this problem? I know it's kind of silly for someone to select an END year before the START year, but well you know...
Hi, the return array for the first xlookup was the year and the return array for the 2nd xlookup was the games so why did xlookup return the whole table? How did it know to return the columns in between? Does the colon operator say give me all the columns between the two return arrays?
@@excelisfun I am a big Blue Jay fan so thanks for the Josh Donaldson trade some years ago! If there is any consolation - we traded Liam Hendricks to you guys on a separate deal :)
@@derekcanmexit O, Blue Jays!!!!! I remember Ricky Henderson single handedly annihilating the Blue Jays in a Championship series in late 1980s, can't remember the exact year and then later, Rickey Henderson played for Blue Jays. I also remember Joe Carter game winning home run in 1993 for the Blue Jays in World. Baseball is fun!!!!
@@excelisfun Henderson was definitely a game changer. Always dreaded when he came up to bat vs. the Jays and I can't believe he played until he was 44! Amazing! Also, credit to the A's for consistently making the playoffs as a small market team! Billy Beane is a genius - loved reading about him in Moneyball!
Suggestion for a sequel. Same exercise but return a 5th column that shows how many games the loser won. Note: I did not try this so I am not sure how it will work exactly but it should be doable and fun for the crazy folk like me.
The best Excel channel on RUclips. Thanks for publishing!
You are welcome for the publish!!!!
Great video!!!Filter is efficient, xlookup is beautiful , offset is visual. There are endless approaches. Other couple of them:
=FILTER(B12:E128,ISNUMBER(XMATCH(B12:B128,SEQUENCE(C5-C4+1,,C4))))
=INDIRECT("B"&11+XMATCH(C4,B12:B128)):INDIRECT("e"&11+XMATCH(C5,B12:B128))
=INDEX(B12:B128,MATCH(C4,B12:B128,0)):INDEX(E12:E128,MATCH(C5,B12:B128,0))
Who likes filter likes science, who likes xlookup likes art😊
Thanks, Teammate!!!! I added your formulas to the download workbook : )
The good old: INDEX:INDEX, before we had XLOOKUP!!!
every day I learn more thank you
Interesting use of XLOOKUP! Brilliant!
Also it's very good to highlight the impact of sorting on the output of three different approaches. Super!
Thanks, MF Wong : )
😍 Love the FILTER.
Some more alternative formulas
=FILTER(B12:E128,1-(B12:B128C5))
=FILTER(B12:E128,ABS(B12:B128-(C4+C5)/2)
LOVE 'em!!!!!! Those are fun : )
I added your formulas to the download workbook : ) Go Team!!!!
Thank you 😍
@@ExcelWizard Such a great Team : )
What video quality! Exceptional explanation. I love the part where you compare the different methods. Thank you for sharing it Mike!
You are welcome for the share!!!!
Glad you like the share and compare, Ivan : ) : )
What I know about excel is thanks to you, Mike. I'm learning so much and using every day in my job to solve many challenges! Each formula has new tricks! Thank you!
Great use of XLOOKUP, Mike!! :-)))
From my side almost old way (almost because of SEQUENCE and dynamic arrays)
=INDEX(B12:E128,MATCH(SEQUENCE(C5-C4+1,,C4),B12:B128,0),SEQUENCE(,4))
This can be used for both a sorted and unsorted range (but only if we have a year column with unique values).
Once again, thanks for a great video, Mike :-))))
It's a nice function. It's a real shame that INDEX didn't ignore FALSE (or Zero, negative or NA) values. As far as I can see, FILTER() is just an INDEX function that allows for that. For example, if I used =INDEX(B12:E28,ROW(B12:B128)*(B12:B128>=C4)*(B12:B128
Great Formula, Bill Szysz!!!! Excel Wizard did this one too : )
@@ricos1497 Just a fun note: back in the VERY first edition of Excel, back when it was Multiplan not Excel yet, INDEX was the second lookup function, after LOOKUP, but before VLOOKUP : )
I added your cool formula to the download workbook, Bill Szysz : ) : )
@@excelisfun it makes total sense that it would be, it's such a logical function on a spreadsheet. I'm surprised they even bothered with either lookup and didn't just rely on the match function in the row and column criteria. I suppose it's far more obvious now that there are dynamic arrays that return tables of data.
Great work, i prefer filter. Thanks Mike!👍👍👍
Yes, FILTER is fabulous!!!!
Thanks Mike...I liked Filter the most but xlookup is up on my list too. Thanks for the video. Good luck with progress on the book. Looking forward to it whenever you finish it...
Book is going REALLY slow. 2 pages a day... : ( But I am on chapter 14, page 320 of about 700...
I prefer FILTER because of its versatility (robustness) and the fact that its filter logic seems more intuitive to me.
I just wish we could use wildcards, though, just like we can in the *IFS-functions.
You can use "sort of wild cards", if we use SEARCH or FIND...
FILTER is definitely robust : )
@@excelisfun Depending on what you are trying to do, you can use IFS functions in the filter criteria...
@@excelisfun I know Mike, it’s just that in SUMIFS, for instance, we don’t even have to, we can just use “Soyuz*” as a criterion.
Very elegant - no need to ‘stack’ functions (so much).
@@GeertDelmulle Yes, very elegant : )
@@t.pigeon2384 To be absolutely clear, with *IFS-functions, i mean functions like SUMIFS, AVERAGEIFS, etc., not the IFS-function itself. That’s a different kind of function. If those are the kind of functions you were thinking of as well, then... I fail to see exactly how you would use those functions in the filter criteria. Can you give an example?
Awesome teachings as usual!
Fun is fun, especially with Excel : ) Glad you like my videos, Pete!!!
This is super amazing and XLookUp is just getting crazier day after day...
Dope.... I'm happy I got my M365, now I can try all your lessons from day 1 of your M365 lessons
Thanks sir... You're super amazing
Glad to help!
Great work Mike!
Glad you like it, Chris M!!!! Thanks for your consistent Teammateshipness : )
Informative as always! Great examples. Thanks Mike!
You are welcome, Roberto!!!!
A great tutorial and very well explained. Thanks Mike..
You are welcome, K D : )
Amazing one! Very very informative 👏 Thank you Mike 😊
You are welcome!!!
Perfect and Amazing ... thanks Mike
You are welcome, Hussein!!!
Great tips & comparison. Thanks, Mike
You are welcome, Jim : )
nice Mike Thanks
You are welcome, Sevag!!!
Great work. I'd go xlookup too, there's just something nice about it. I love the fact it returns a cell reference. It's just good.
Yes, VLOOKUP and LOOKUP can't do that. I like it too : )
DR Steele pointed out a problem with Xlookup in this situation. Check his comment.
@@t.pigeon2384 yep, Mike mentioned the sort issue in the video. I'd still use it though, I'd just be aware of its limitations. Although these days, I'm certain I'd have all that data in power query anyway, so that users couldn't break it by sorting!
@@ricos1497 whoops didn't watch the video :) I always try to do the magic tricks without or looking at answers and if my answers match, I skip the video. Sorry Mike!!!
@@ricos1497 Power Query is good that way : )
Filter is so neat. I fail to have ready a set of criteria to use in the "include" argument of FILTER. For example you used the AND logical test (B12:B128>=C4)*(B12:B128
When you learn statistics and boolean math, then you learn that * is AND Logical Test and + is OR Logical Test.
Amazing...
Glad it is amazing for you, CA V G!!!!
AWESOOOOME xlookup and offset tricks. Super love this!
Glad you super love 'em, Edge!!!
Wow....Great guru ...👍
Glad you like it, Tulsidas!!!
Awesome tips!
Yes
FIlter is nice for all Conditions.
Yes, indeed!!!
Super vid, Mike! I think FILTER works best when using Excel Tables because they are frequently sorted by users in various ways and have columns inserted and deleted. Also. I've been coloring the cell containing a spill formula a certain way - diagonal shading from the top left to bottom right in the cell. It makes it easy to identify the spilling formulas.
Extremely good point. Filter is the only formula that works no matter what basically. I would propose to modify the solution as follows.
=SORT(FILTER(B12:E128,(B12:B128>=C4)*(B12:B128
The sort is obviously not needed except to make the results sorted by Year (or whatever)
Yes, Excel Table is the way to make it purely dynamic!!!!
Amazing as always👍 Thank you for sharing😃👍
You are welcome for the share!
GREAT COMPARISON!
Glad it was so, Steven!
FILTER wins : )
In a case like this (filtering between two sorted limits), Xlookup is the easiest to understand just by looking at it. Filter would be my next choice. With LET, any of these will work even better
Yes, LET is even better!!!
Great, just learned how use AND inside a filter function... THANKS!!
Yes!!!! AND inside Filter is indispensable : ) AND = * OR = +
AND logical Test = multiplication = *
OR logical Test = addition = +
Thank you for the nice vid.
3. (00:35) FILTER function.
6. (05:06) Compare all methods.
7. (05:48) Summary, Closing and Video Links
Thanks : )
This is brilliant but the non 365 users will not be able to use either of these solutions since the offset is using the xmatch. Regardless of the 365 impediment these are great solutions.
You can use MATCH rather than XMATCH : )
Wonderful Video
Glad it is wonderful!
Thanks Mike. Always amazingly done!!! :)
Glad you like it, Formula Guy : )
Like always, EXCELlent video. Thanks Mike for the share.
You are welcome for the share, Syed M M : )
Boom!Loving These Great Formulas,I Would Go XLOOKUP Too Such A Cool Function...Thank You Mike :)
Boom: XLOOKUP!!!!
Impressive!
Gla you like it : )
great video Mike thanks for knowledge share , and ...no preference for formulas
so what I understand let the context decide :)
Context does decide everything in the end ; )
Why haven't I seen this one before? , all three methods, wonderful, well perhaps not filter, but using using xlookup to return an array was something I had no idea about. I don't think it's in the MS docs?
But... FILTER is the only one that can handle any type of sort, and you know what we people do with data. Colon operator for looks like INDEX and XLOOKUP have been around forever, but since Microsoft Help is so horrible a lot of the time (like what are the people in the help department at Microsoft even getting paid for), this does not show up in a lot of documentation. But, in this particular situation, the colon operator does show up in the current Microsoft Help for XLOOKUP.
FILTER!! FILTER was made for this situation. XL a strong contender but a bit more complex for this situation. OFFSET? Participation Trophy!! LOL.
lol... FILTER is the winner ; )
Can we also use index with the small function with and 2 ifs conditions which should work similar to the filter function?
I don't know if this should count because it requires an area the size of the table to spill properly, but here goes...
=SORT(IF((B12:B128>=C4)*(B12:B128
Let version with unique wrapper to get rid of blanks.
=LET(r,B12:B128,SORT(UNIQUE((IF((r>=C4)*(r
If you don't care about sorting
=LET(r,B12:B128,UNIQUE(IF((r>=C4)*(r
=UNIQUE(IF((B12:B128>=C4)*(B12:B128
Sweet! However, if the lookup table is sorted by year, only XLOOKUP works and displays the full table of values between the Start Year and earlier End Year. Disappointingly FILTER returns #N/A (although a good hint!), and OFFSET returns only the Start Year. FILTER is still best, but is there a way to restrict the Start and End years from this problem? I know it's kind of silly for someone to select an END year before the START year, but well you know...
Yes, we could use data validation : )
Hi, the return array for the first xlookup was the year and the return array for the 2nd xlookup was the games so why did xlookup return the whole table? How did it know to return the columns in between? Does the colon operator say give me all the columns between the two return arrays?
Microsoft programmed the colon operator to grab all cells between the first and second XLOOKUP.
Great comparisons! Out of curiosity - which team do you usually root for?
Oakland is where I grew up. I am Oakland A's, Raiders and Warriors to the max : )
@@excelisfun I am a big Blue Jay fan so thanks for the Josh Donaldson trade some years ago! If there is any consolation - we traded Liam Hendricks to you guys on a separate deal :)
@@derekcanmexit O, Blue Jays!!!!! I remember Ricky Henderson single handedly annihilating the Blue Jays in a Championship series in late 1980s, can't remember the exact year and then later, Rickey Henderson played for Blue Jays. I also remember Joe Carter game winning home run in 1993 for the Blue Jays in World. Baseball is fun!!!!
@@derekcanmexit You are welcome for Josh Donaldson!!!
@@excelisfun Henderson was definitely a game changer. Always dreaded when he came up to bat vs. the Jays and I can't believe he played until he was 44! Amazing! Also, credit to the A's for consistently making the playoffs as a small market team! Billy Beane is a genius - loved reading about him in Moneyball!
What excel version you using? Thank you
Microsoft 365.
Just 'cause I need LET practice :)
=LET(l,B12:B128,t,B12:E128,XLOOKUP(C4,l,t):XLOOKUP(C5,l,t))
Suggestion for a sequel. Same exercise but return a 5th column that shows how many games the loser won. Note: I did not try this so I am not sure how it will work exactly but it should be doable and fun for the crazy folk like me.
LET is good!
I added your formulas to the download workbook : ) Go Team!!!!
please help me
name Qty
Pop 5
Joy 3
Lila 1
Append result
pop
pop
pop
pop
pop
joy
joy
joy
Lila