OFFSET, XLOOKUP or FILTER function for Extracting Baseball Data? Excel Magic Trick 1697.

No video

OFFSET, XLOOKUP or FILTER function for Extracting Baseball Data? Excel Magic Trick 1697.

Рет қаралды 17,100

excelisfun

Күн бұрын

Пікірлер: 141

@hautecommercialrealestate2114 3 жыл бұрын

The best Excel channel on KZfaq. Thanks for publishing!

@excelisfun 3 жыл бұрын

You are welcome for the publish!!!!

@fernando5166 11 ай бұрын

every day I learn more thank you

@Excelambda 3 жыл бұрын

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😊

@excelisfun 3 жыл бұрын

Thanks, Teammate!!!! I added your formulas to the download workbook : )

@excelisfun 3 жыл бұрын

The good old: INDEX:INDEX, before we had XLOOKUP!!!

@wmfexcel 3 жыл бұрын

Interesting use of XLOOKUP! Brilliant! Also it's very good to highlight the impact of sorting on the output of three different approaches. Super!

@excelisfun 3 жыл бұрын

Thanks, MF Wong : )

@Luciano_mp 3 жыл бұрын

Great work, i prefer filter. Thanks Mike!👍👍👍

@excelisfun 3 жыл бұрын

Yes, FILTER is fabulous!!!!

@ExcelWizard 3 жыл бұрын

😍 Love the FILTER. Some more alternative formulas =FILTER(B12:E128,1-(B12:B128C5)) =FILTER(B12:E128,ABS(B12:B128-(C4+C5)/2)

@excelisfun 3 жыл бұрын

LOVE 'em!!!!!! Those are fun : )

@excelisfun 3 жыл бұрын

I added your formulas to the download workbook : ) Go Team!!!!

@ExcelWizard 3 жыл бұрын

Thank you 😍

@excelisfun 3 жыл бұрын

@@ExcelWizard Such a great Team : )

@xandesk 2 жыл бұрын

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!

@BillSzysz1 3 жыл бұрын

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 :-))))

@ricos1497 3 жыл бұрын

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

@excelisfun 3 жыл бұрын

Great Formula, Bill Szysz!!!! Excel Wizard did this one too : )

@excelisfun 3 жыл бұрын

@@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 3 жыл бұрын

I added your cool formula to the download workbook, Bill Szysz : ) : )

@ricos1497 3 жыл бұрын

@@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_ES 3 жыл бұрын

What video quality! Exceptional explanation. I love the part where you compare the different methods. Thank you for sharing it Mike!

@excelisfun 3 жыл бұрын

You are welcome for the share!!!!

@excelisfun 3 жыл бұрын

Glad you like the share and compare, Ivan : ) : )

@sasavienne 3 жыл бұрын

A great tutorial and very well explained. Thanks Mike..

@excelisfun3903 3 жыл бұрын

You are welcome, K D : )

@petecardona8203 Жыл бұрын

Awesome teachings as usual!

@excelisfun Жыл бұрын

Fun is fun, especially with Excel : ) Glad you like my videos, Pete!!!

@mattschoular8844 3 жыл бұрын

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...

@excelisfun 3 жыл бұрын

Book is going REALLY slow. 2 pages a day... : ( But I am on chapter 14, page 320 of about 700...

@HusseinKorish 3 жыл бұрын

Perfect and Amazing ... thanks Mike

@excelisfun 3 жыл бұрын

You are welcome, Hussein!!!

@GeertDelmulle 3 жыл бұрын

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 3 жыл бұрын

You can use "sort of wild cards", if we use SEARCH or FIND... FILTER is definitely robust : )

@t.pigeon2384 3 жыл бұрын

@@excelisfun Depending on what you are trying to do, you can use IFS functions in the filter criteria...

@GeertDelmulle 3 жыл бұрын

@@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).

@excelisfun 3 жыл бұрын

@@GeertDelmulle Yes, very elegant : )

@GeertDelmulle 3 жыл бұрын

@@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?

@jimfitch 3 жыл бұрын

Great tips & comparison. Thanks, Mike

@excelisfun 3 жыл бұрын

You are welcome, Jim : )

@omotossso 3 жыл бұрын

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

@excelisfun 3 жыл бұрын

Glad to help!

@chrism9037 3 жыл бұрын

Great work Mike!

@excelisfun 3 жыл бұрын

Glad you like it, Chris M!!!! Thanks for your consistent Teammateshipness : )

@robertovelicaz7719 3 жыл бұрын

Informative as always! Great examples. Thanks Mike!

@excelisfun 3 жыл бұрын

You are welcome, Roberto!!!!

@VivekGupta1994 3 жыл бұрын

Amazing...

@excelisfun 3 жыл бұрын

Glad it is amazing for you, CA V G!!!!

@nadermounir8228 3 жыл бұрын

Amazing one! Very very informative 👏 Thank you Mike 😊

@excelisfun 3 жыл бұрын

You are welcome!!!

@sevagjb 3 жыл бұрын

nice Mike Thanks

@excelisfun 3 жыл бұрын

You are welcome, Sevag!!!

@ricos1497 3 жыл бұрын

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.

@excelisfun 3 жыл бұрын

Yes, VLOOKUP and LOOKUP can't do that. I like it too : )

@t.pigeon2384 3 жыл бұрын

DR Steele pointed out a problem with Xlookup in this situation. Check his comment.

@ricos1497 3 жыл бұрын

@@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.pigeon2384 3 жыл бұрын

@@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!!!

@excelisfun 3 жыл бұрын

@@ricos1497 Power Query is good that way : )

@simfinso858 3 жыл бұрын

Yes FIlter is nice for all Conditions.

@excelisfun 3 жыл бұрын

Yes, indeed!!!

@edge5817 3 жыл бұрын

AWESOOOOME xlookup and offset tricks. Super love this!

@excelisfun 3 жыл бұрын

Glad you super love 'em, Edge!!!

@nsanch0181 3 жыл бұрын

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

@excelisfun 3 жыл бұрын

When you learn statistics and boolean math, then you learn that * is AND Logical Test and + is OR Logical Test.

@petecardona8203 Жыл бұрын

Awesome tips!

@christoslefkimiotis9889 2 жыл бұрын

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.

@excelisfun 2 жыл бұрын

You can use MATCH rather than XMATCH : )

@tulsidasjamnani9455 3 жыл бұрын

Wow....Great guru ...👍

@excelisfun 3 жыл бұрын

Glad you like it, Tulsidas!!!

@stevennye5075 3 жыл бұрын

GREAT COMPARISON!

@excelisfun 3 жыл бұрын

Glad it was so, Steven!

@excelisfun 3 жыл бұрын

FILTER wins : )

@Bhavik_Khatri Жыл бұрын

Thank you for the nice vid.

@AdrianMartinezOK 3 жыл бұрын

Great, just learned how use AND inside a filter function... THANKS!!

@excelisfun 3 жыл бұрын

Yes!!!! AND inside Filter is indispensable : ) AND = * OR = +

@excelisfun 3 жыл бұрын

AND logical Test = multiplication = * OR logical Test = addition = +

@drsteele4749 3 жыл бұрын

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.pigeon2384 3 жыл бұрын

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.pigeon2384 3 жыл бұрын

The sort is obviously not needed except to make the results sorted by Year (or whatever)

@excelisfun 3 жыл бұрын

Yes, Excel Table is the way to make it purely dynamic!!!!

@davorercegovac3674 3 жыл бұрын

Amazing as always👍 Thank you for sharing😃👍

@excelisfun 3 жыл бұрын

You are welcome for the share!

@patrickschardt7724 3 жыл бұрын

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

@excelisfun 3 жыл бұрын

Yes, LET is even better!!!

@sachinrv1 3 жыл бұрын

Wonderful Video

@excelisfun 3 жыл бұрын

Glad it is wonderful!

@darrylmorgan 3 жыл бұрын

Boom!Loving These Great Formulas,I Would Go XLOOKUP Too Such A Cool Function...Thank You Mike :)

@excelisfun 3 жыл бұрын

Boom: XLOOKUP!!!!

@johnborg5419 3 жыл бұрын

Thanks Mike. Always amazingly done!!! :)

@excelisfun 3 жыл бұрын

Glad you like it, Formula Guy : )

@SyedMuzammilMahasanShahi 3 жыл бұрын

Like always, EXCELlent video. Thanks Mike for the share.

@excelisfun 3 жыл бұрын

You are welcome for the share, Syed M M : )

@Muuip 3 жыл бұрын

Impressive!

@excelisfun 3 жыл бұрын

Gla you like it : )

@DIGITAL_COOKING 3 жыл бұрын

great video Mike thanks for knowledge share , and ...no preference for formulas so what I understand let the context decide :)

@excelisfun 3 жыл бұрын

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.

@nadermounir8228 3 жыл бұрын

Can we also use index with the small function with and 2 ifs conditions which should work similar to the filter function?

@richardhay645 3 жыл бұрын

FILTER!! FILTER was made for this situation. XL a strong contender but a bit more complex for this situation. OFFSET? Participation Trophy!! LOL.

@excelisfun 3 жыл бұрын

lol... FILTER is the winner ; )

@planxlsm 3 жыл бұрын

3. (00:35) FILTER function. 6. (05:06) Compare all methods. 7. (05:48) Summary, Closing and Video Links

@excelisfun 3 жыл бұрын

Thanks : )

@t.pigeon2384 3 жыл бұрын

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.pigeon2384 3 жыл бұрын

Let version with unique wrapper to get rid of blanks. =LET(r,B12:B128,SORT(UNIQUE((IF((r>=C4)*(r

@t.pigeon2384 3 жыл бұрын

If you don't care about sorting =LET(r,B12:B128,UNIQUE(IF((r>=C4)*(r

@t.pigeon2384 3 жыл бұрын

=UNIQUE(IF((B12:B128>=C4)*(B12:B128

@derekcanmexit 3 жыл бұрын

Great comparisons! Out of curiosity - which team do you usually root for?

@excelisfun 3 жыл бұрын

Oakland is where I grew up. I am Oakland A's, Raiders and Warriors to the max : )

@derekcanmexit 3 жыл бұрын

@@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 :)

@excelisfun 3 жыл бұрын

@@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 3 жыл бұрын

@@derekcanmexit You are welcome for Josh Donaldson!!!

@derekcanmexit 3 жыл бұрын

@@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!

@jerrydellasala7643 3 жыл бұрын

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...

@excelisfun 3 жыл бұрын

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.

@Aion69z 3 жыл бұрын

What excel version you using? Thank you

@excelisfun 3 жыл бұрын

Microsoft 365.

@t.pigeon2384 3 жыл бұрын

Just 'cause I need LET practice :) =LET(l,B12:B128,t,B12:E128,XLOOKUP(C4,l,t):XLOOKUP(C5,l,t))

@t.pigeon2384 3 жыл бұрын

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.

@excelisfun 3 жыл бұрын

LET is good!

@excelisfun 3 жыл бұрын

I added your formulas to the download workbook : ) Go Team!!!!