The best Excel channel on KZfaq. Thanks for publishing!
@excelisfun3 жыл бұрын
You are welcome for the publish!!!!
@fernando516611 ай бұрын
every day I learn more thank you
@Excelambda3 жыл бұрын
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😊
@excelisfun3 жыл бұрын
Thanks, Teammate!!!! I added your formulas to the download workbook : )
@excelisfun3 жыл бұрын
The good old: INDEX:INDEX, before we had XLOOKUP!!!
@wmfexcel3 жыл бұрын
Interesting use of XLOOKUP! Brilliant! Also it's very good to highlight the impact of sorting on the output of three different approaches. Super!
@excelisfun3 жыл бұрын
Thanks, MF Wong : )
@Luciano_mp3 жыл бұрын
Great work, i prefer filter. Thanks Mike!👍👍👍
@excelisfun3 жыл бұрын
Yes, FILTER is fabulous!!!!
@ExcelWizard3 жыл бұрын
😍 Love the FILTER. Some more alternative formulas =FILTER(B12:E128,1-(B12:B128C5)) =FILTER(B12:E128,ABS(B12:B128-(C4+C5)/2)
@excelisfun3 жыл бұрын
LOVE 'em!!!!!! Those are fun : )
@excelisfun3 жыл бұрын
I added your formulas to the download workbook : ) Go Team!!!!
@ExcelWizard3 жыл бұрын
Thank you 😍
@excelisfun3 жыл бұрын
@@ExcelWizard Such a great Team : )
@xandesk2 жыл бұрын
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!
@BillSzysz13 жыл бұрын
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 :-))))
@ricos14973 жыл бұрын
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
@excelisfun3 жыл бұрын
Great Formula, Bill Szysz!!!! Excel Wizard did this one too : )
@excelisfun3 жыл бұрын
@@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 : )
@excelisfun3 жыл бұрын
I added your cool formula to the download workbook, Bill Szysz : ) : )
@ricos14973 жыл бұрын
@@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.
@IvanCortinas_ES3 жыл бұрын
What video quality! Exceptional explanation. I love the part where you compare the different methods. Thank you for sharing it Mike!
@excelisfun3 жыл бұрын
You are welcome for the share!!!!
@excelisfun3 жыл бұрын
Glad you like the share and compare, Ivan : ) : )
@sasavienne3 жыл бұрын
A great tutorial and very well explained. Thanks Mike..
@excelisfun39033 жыл бұрын
You are welcome, K D : )
@petecardona8203 Жыл бұрын
Awesome teachings as usual!
@excelisfun Жыл бұрын
Fun is fun, especially with Excel : ) Glad you like my videos, Pete!!!
@mattschoular88443 жыл бұрын
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...
@excelisfun3 жыл бұрын
Book is going REALLY slow. 2 pages a day... : ( But I am on chapter 14, page 320 of about 700...
@HusseinKorish3 жыл бұрын
Perfect and Amazing ... thanks Mike
@excelisfun3 жыл бұрын
You are welcome, Hussein!!!
@GeertDelmulle3 жыл бұрын
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.
@excelisfun3 жыл бұрын
You can use "sort of wild cards", if we use SEARCH or FIND... FILTER is definitely robust : )
@t.pigeon23843 жыл бұрын
@@excelisfun Depending on what you are trying to do, you can use IFS functions in the filter criteria...
@GeertDelmulle3 жыл бұрын
@@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).
@excelisfun3 жыл бұрын
@@GeertDelmulle Yes, very elegant : )
@GeertDelmulle3 жыл бұрын
@@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?
@jimfitch3 жыл бұрын
Great tips & comparison. Thanks, Mike
@excelisfun3 жыл бұрын
You are welcome, Jim : )
@omotossso3 жыл бұрын
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
@excelisfun3 жыл бұрын
Glad to help!
@chrism90373 жыл бұрын
Great work Mike!
@excelisfun3 жыл бұрын
Glad you like it, Chris M!!!! Thanks for your consistent Teammateshipness : )
@robertovelicaz77193 жыл бұрын
Informative as always! Great examples. Thanks Mike!
@excelisfun3 жыл бұрын
You are welcome, Roberto!!!!
@VivekGupta19943 жыл бұрын
Amazing...
@excelisfun3 жыл бұрын
Glad it is amazing for you, CA V G!!!!
@nadermounir82283 жыл бұрын
Amazing one! Very very informative 👏 Thank you Mike 😊
@excelisfun3 жыл бұрын
You are welcome!!!
@sevagjb3 жыл бұрын
nice Mike Thanks
@excelisfun3 жыл бұрын
You are welcome, Sevag!!!
@ricos14973 жыл бұрын
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.
@excelisfun3 жыл бұрын
Yes, VLOOKUP and LOOKUP can't do that. I like it too : )
@t.pigeon23843 жыл бұрын
DR Steele pointed out a problem with Xlookup in this situation. Check his comment.
@ricos14973 жыл бұрын
@@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!
@t.pigeon23843 жыл бұрын
@@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!!!
@excelisfun3 жыл бұрын
@@ricos1497 Power Query is good that way : )
@simfinso8583 жыл бұрын
Yes FIlter is nice for all Conditions.
@excelisfun3 жыл бұрын
Yes, indeed!!!
@edge58173 жыл бұрын
AWESOOOOME xlookup and offset tricks. Super love this!
@excelisfun3 жыл бұрын
Glad you super love 'em, Edge!!!
@nsanch01813 жыл бұрын
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
@excelisfun3 жыл бұрын
When you learn statistics and boolean math, then you learn that * is AND Logical Test and + is OR Logical Test.
@petecardona8203 Жыл бұрын
Awesome tips!
@christoslefkimiotis98892 жыл бұрын
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.
@excelisfun2 жыл бұрын
You can use MATCH rather than XMATCH : )
@tulsidasjamnani94553 жыл бұрын
Wow....Great guru ...👍
@excelisfun3 жыл бұрын
Glad you like it, Tulsidas!!!
@stevennye50753 жыл бұрын
GREAT COMPARISON!
@excelisfun3 жыл бұрын
Glad it was so, Steven!
@excelisfun3 жыл бұрын
FILTER wins : )
@Bhavik_Khatri Жыл бұрын
Thank you for the nice vid.
@AdrianMartinezOK3 жыл бұрын
Great, just learned how use AND inside a filter function... THANKS!!
@excelisfun3 жыл бұрын
Yes!!!! AND inside Filter is indispensable : ) AND = * OR = +
@excelisfun3 жыл бұрын
AND logical Test = multiplication = * OR logical Test = addition = +
@drsteele47493 жыл бұрын
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.
@t.pigeon23843 жыл бұрын
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
@t.pigeon23843 жыл бұрын
The sort is obviously not needed except to make the results sorted by Year (or whatever)
@excelisfun3 жыл бұрын
Yes, Excel Table is the way to make it purely dynamic!!!!
@davorercegovac36743 жыл бұрын
Amazing as always👍 Thank you for sharing😃👍
@excelisfun3 жыл бұрын
You are welcome for the share!
@patrickschardt77243 жыл бұрын
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
@excelisfun3 жыл бұрын
Yes, LET is even better!!!
@sachinrv13 жыл бұрын
Wonderful Video
@excelisfun3 жыл бұрын
Glad it is wonderful!
@darrylmorgan3 жыл бұрын
Boom!Loving These Great Formulas,I Would Go XLOOKUP Too Such A Cool Function...Thank You Mike :)
@excelisfun3 жыл бұрын
Boom: XLOOKUP!!!!
@johnborg54193 жыл бұрын
Thanks Mike. Always amazingly done!!! :)
@excelisfun3 жыл бұрын
Glad you like it, Formula Guy : )
@SyedMuzammilMahasanShahi3 жыл бұрын
Like always, EXCELlent video. Thanks Mike for the share.
@excelisfun3 жыл бұрын
You are welcome for the share, Syed M M : )
@Muuip3 жыл бұрын
Impressive!
@excelisfun3 жыл бұрын
Gla you like it : )
@DIGITAL_COOKING3 жыл бұрын
great video Mike thanks for knowledge share , and ...no preference for formulas so what I understand let the context decide :)
@excelisfun3 жыл бұрын
Context does decide everything in the end ; )
@williamarthur4801 Жыл бұрын
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?
@excelisfun Жыл бұрын
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.
@nadermounir82283 жыл бұрын
Can we also use index with the small function with and 2 ifs conditions which should work similar to the filter function?
@richardhay6453 жыл бұрын
FILTER!! FILTER was made for this situation. XL a strong contender but a bit more complex for this situation. OFFSET? Participation Trophy!! LOL.
@excelisfun3 жыл бұрын
lol... FILTER is the winner ; )
@planxlsm3 жыл бұрын
3. (00:35) FILTER function. 6. (05:06) Compare all methods. 7. (05:48) Summary, Closing and Video Links
@excelisfun3 жыл бұрын
Thanks : )
@t.pigeon23843 жыл бұрын
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
@t.pigeon23843 жыл бұрын
Let version with unique wrapper to get rid of blanks. =LET(r,B12:B128,SORT(UNIQUE((IF((r>=C4)*(r
@t.pigeon23843 жыл бұрын
If you don't care about sorting =LET(r,B12:B128,UNIQUE(IF((r>=C4)*(r
@t.pigeon23843 жыл бұрын
=UNIQUE(IF((B12:B128>=C4)*(B12:B128
@derekcanmexit3 жыл бұрын
Great comparisons! Out of curiosity - which team do you usually root for?
@excelisfun3 жыл бұрын
Oakland is where I grew up. I am Oakland A's, Raiders and Warriors to the max : )
@derekcanmexit3 жыл бұрын
@@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 :)
@excelisfun3 жыл бұрын
@@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!!!!
@excelisfun3 жыл бұрын
@@derekcanmexit You are welcome for Josh Donaldson!!!
@derekcanmexit3 жыл бұрын
@@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!
@jerrydellasala76433 жыл бұрын
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...
@excelisfun3 жыл бұрын
Yes, we could use data validation : )
@gijeet5374 Жыл бұрын
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 Жыл бұрын
Microsoft programmed the colon operator to grab all cells between the first and second XLOOKUP.
@Aion69z3 жыл бұрын
What excel version you using? Thank you
@excelisfun3 жыл бұрын
Microsoft 365.
@t.pigeon23843 жыл бұрын
Just 'cause I need LET practice :) =LET(l,B12:B128,t,B12:E128,XLOOKUP(C4,l,t):XLOOKUP(C5,l,t))
@t.pigeon23843 жыл бұрын
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.
@excelisfun3 жыл бұрын
LET is good!
@excelisfun3 жыл бұрын
I added your formulas to the download workbook : ) Go Team!!!!
@executiveenigneer44033 жыл бұрын
please help me name Qty Pop 5 Joy 3 Lila 1 Append result pop pop pop pop pop joy joy joy Lila