FILTER Function or Advanced Filter to Extract Records with Complex Filter? Excel Magic Trick 1841

Learn about how to use FILTER function with a complex filter based on a BETWEEN, AND and OR Logical Tests. Chapter 13 in the book, The Only App That Matters. Learn about Advanced Filter also.
Download Excel File: excelisfun.net/files/EMT1841....
Topics:
1. (00:00) Introduction
2. (00:05) Complex Filter
3. (00:30) Logical Tests
4. (02:00) Filter Function
5. (06:02) Advanced Filter)
6. (07:54) Summary
7. (08:20) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lookup #xlookup #filter #filterfunction #logical #advancedfilter

[@richardhay645]

For a long time I was a big fan/user of Advanced Filter and SUMPRODUCT for "complex" filtering. BUT I haven't pressed Alt,A,Q since Fall 2018!!! Kinda like getting a new mattress--how soon we forget the old one!

[@ExcelHechoFacil]

Mike cada video tuyo es una enseñanza completa, un contenido invaluable. Gracias por seguir. Un abrazo fuerte.

[@roywilson9580]

Thanks for the video, Mike. Was great to see someone using advanced filter; it is one of my favourites from way back when, and I still use it sometimes. I do hope that the excel team don't deprecate it in favour of =FILTER!

[@kebincui]

Great as always.Thanks Mike

[@user-ju1it8wf2j]

Straight to the point. No nonsense! That's Mike! Thank you!

[@kiwikiow]

Thank you Mike for a cool Excel trick. :)

[@gilbertosegoviano7701]

You are the best Mike
Thanks for sharing 😊

[@barttitulaerexcelbart9400]

Very nice to see the "old" Advanced Filter also working. Although the newborn FILTER gets a lot of attention nowadays worldwide, the old tools still do their job! Nice to see you respect these old methods as well ....😉

[@baruchschwartz819]

What a teacher!

[@excelisfun]

Glad to help!!

[@andrewjohnson4352]

I will be coming back to this one!

[@chrism9037]

As always, an outstanding video Mike, thanks!

[@HusseinKorish]

That's absolutely AMAZING Mike ... but i miss the excel file too

[@excelisfun]

Thanks Teammate. I just added.

[@johnborg5419]

Amazing and FUN!!!! Thanks Mike. :) :)

[@ankursharma6157]

Token of Gratitude!
I hope all is well at home 🤞🏿
Best Wishes!

[@excelisfun]

Thank you so much fort the kind token : ) : ) : ) : ) I am still down in California taking care of my Mom. Thank you for the well wishes and support, Ankursharma!!!!

[@nadermounir8228]

Thank you Mike for this great video. 👍 I hope your mom's health is well. My prayers 🙏

[@AnshupalBantra]

Amazing as always Mike. You are the best.
I usually replace the + or condition with (ISNUMBER(XMATCH(E5:E14,$L$18:$L$19))) if I have to find multiple values.
=FILTER(B5:F14
,( (B5:B14>=H18)*(B5:B14

[@excelisfun]

LOVE it : ) : ) The ISNUMBER/XMATCH rules for more than two OR conditions : )

[@viktorasgolubevas2386]

another known technique:
COUNTIF($L$18:$L$19 , E5:E14)

[@lucaviglio1206]

Super useful, thanks a lot Mike

[@ExcelInstructor]

1:43 i have this book.
I can say its superb Masterpiece of a book.
For writing this Mike deservers at least 5 Nobel prizes :)

[@erdal4687]

Thanks Mike

[@rajuj2548]

Wow great

[@NoShadowOfDoubt1]

Nice!

[@excelisfun]

Long time so see : )

[@fernando5166]

wow, thanks you

[@fokhrulislam2633]

Thank you Sir.

[@reighbernal188]

I have been looking for the same.

[@user-jk5gg8mv6n]

Thank you

[@mohamedchakroun4973]

Yes it is the only app that matter :-)

[@excelisfun]

: ) : ) : )

[@rnunez047]

En espera que lo suelten. Pero sin el error fantasma

[@abdelazizallam]

MR Mike thanks ,
please try as you can to do tricks with power query
plz do it with power query also
thanks ,

[@excelisfun]

it is just a auto filter in Power query. Here is a list of the 202 PQ vids I have done: ua-cam.com/play/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK.html

[@klfa]

As you know Mike! In realworld..;), When filter function meat date It must be come up with headache. especially date fields are blank randomly. ㅜㅜ

[@dschmid8845]

Is there a way to use conditional formatting to highlight rows that match criteria?

[@excelisfun]

Yes, Search my channel. I have about 10 videos :)

[@TheAlwaysGoel]

Hey Mike as always you are awesome I request you can you make python in excel video I am qureous about that concept

[@excelisfun]

I can't for almost a year becasue I am taking care of a sick Mom. But sometime in future : )

[@rtrbs8383]

Hi Sir how do we use byrow/bycol function with RANK.EQ to spill the results rather than dragging

[@excelisfun]

Here is how to spill aggregate calculations:
ua-cam.com/video/qkTAtVqYXeE/v-deo.html

[@rtrbs8383]

Thank you very much.Can we use BYROW and BYCOL together as I have 11rows and 6 Colums @@excelisfun

[@viviendoen360]

Hi there, Cool video but I have a question, what is the difference between this kind of filter and the common kind of filter, the automatic ones.
If I put an automatic filter I can do the same, right? Or not?

[@excelisfun]

Yes.

[@viviendoen360]

@@excelisfun thanks for. Answer me, so, why would someone do this kind of filter?