Excel FILTER Function - Lookup to Return Multiple Values
ฝัง
- เผยแพร่เมื่อ 8 ก.ค. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Are you ready to revolutionize the way you use Excel for data lookups? This informative video covers the powerful capabilities of Excel's FILTER function, a game-changer for data analysis in Excel for Office 365 and 2021.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/filter-file
✨ Key Highlights:
▪️ Multiple Match Results: Learn how FILTER returns multiple matches for your LOOKUP values.
▪️ Dynamic Data Filtering: See how FILTER updates data dynamically without the need for refreshing.
▪️ Versatility: Discover the ease of nesting FILTER in other functions like SUM or COUNT.
▪️ Simplicity and Efficiency: Understand how straightforward it is to write a FILTER formula.
You'd like to do a VLOOKUP but return ALL match results instead of just the first match? You can use Excel's new FILTER function.
What if you'd like to lookup multiple criteria and return ALL match results? You can use the new Excel Dynamic Array FILTER Formula! It's like the dynamic or automatic version of Excel's advanced Filter feature. You can return Filtered results based on AND as well as OR conditions.
The Excel Filter function filters your data based on criteria, and returns all the matching records. This can be one or more records. If more than one match is found, the results will "spill" vertically. If there is a blockage (i.e. your results are spilling into already filled cells) then you will get the #SPILL error. You can easily get rid of this error by removing the cells that are blocking the FILTER formula.
If FILTER doesn't find any matching records it will return a #CALC error. You can suppress this error with the text or value of your choice by adding this to the last argument of the FILTER formula.
* Availability of Excel FILTER function: This feature is available on Excel for Office 365 and Office 2021 - also for Excel on the Web and Mobile.
00:00 How to use Excel's FILTER Function
00:45 Advantages of Excel FILTER Function
01:46 Excel FILTER Function Explained
03:15 Excel FILTER #CALC Error
05:40 #SPILL Error and How to Overcome It
06:13 FILTER Function with Table References
07:14 FILTER Formula with Multiple Lookup Criteria
Return Multiple Match Results (Excel Legacy Method): • Return Multiple Match ...
More about Excel Dynamic Arrays: • Excel Dynamic Arrays (...
Excel Dynamic Arrays Playlist: • Excel for Office 365 &...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
Grab the file I used in the video from here 👉 pages.xelplus.com/filter-file
LOVE the Lavender Filter Visual : ) : ) It is very easy, and fun!!!
Thank you Mike. It definitely is fun!
Your channel is go-to channel for me for all excel-related conundrums. Phenomenal explanation. Once again, you saved me a lot of time and trouble. THANKS A LOT.
Complexity of any functionality becomes simple with your lucid explanation ! Keep up the good work👌
I've no words to express,How supportive n helpful information ur providing to this whole world. Ur rly a legend. I love ur every single lecture.Thank you so much.your the best n hope you'll get all the happiness that u deserve.
Sometimes I feel like pulling you out of the video and giving you a big hug. Thanks Leila. This is very helpful just like all your videos.
I have used it regularly since November 2018. It's easy, useful and efficient. I use Adv Filter occasionally, but usually FILTER.
Leila, on behalf of your Yubtube audience, it is accurate to say that your beauty is ONLY exceeded by your intellect. I love all your videos. You help me and my company daily with the content you post. Please keep them coming!
Once again Ms. Leila, you have saved a lot of time for me in my day to day work. Thanks a million times, God bless you.
Slowly your channel is becoming one of my favorites, seriously I always get happy when I see a new video.
That's great! I'm glad to hear that.
Why can't others explain it the way you do. You are making complex functions feels really simple. Thank you!
I was able to take about a dozen rows with missing dates and find, in an excel file exceeding 9500+ rows, that only 2 rows needed to be updated. Without this video I would have to filter each cell code 1by1 to check to see if the date was missing or not then update. Saved me a good amount of time, thank you.
Teacher of the year...
This is amazing, easy to pull data view of a master table! And great tips and tricks as always, Leila.
This one video just saved me from having to write a Access Database to pull forward all the matches. While Access is easy enough to tackle this project, training users to look things up would take forever, and reports, forms, menus etc., to do simple lookups for a quick query we do all the time, made my task a lot easier. Thank you so so much !
My pleasure. I'm glad it's helpful.
Filter function is awesome.. am able to count unique records when combined with "unique" and "count" functions .. no more complex sumproduct,1/countifs , if you know what I mean. Thank you Leila.. this was so simple to understand.
Love your videos where you explain in such an easy way, have learned as much for a few days as I did for 1 year of search in books
A great method to make a dedicated sheet to search big data tables. Thank you Layla for the clear explanation.
You are always and always blowing my mind away. Every video I watched only one word to say is Wowowow. Please allow me to say thank you for sharing your knowledge with me. I don’t know about excel and my boss gave the assignment to create a a dashboard and it took me 3 months already. I am still struggling with it. Thanks so much
This may well be one of the best functions yet as it provides multiple solutions and is easy to use. Thanks Leila!
It definitely is one of my favorite functions!
THANK YOU THANK YOU THANK YOU - your video and instructions was absolutely brilliant and allowed me to create a multi filter search in excel. In your explanation you used the * symbol as OR, but I also needed 'as well' and I noticed that you'd put a note on the screen that that would be a + symbol. It's that attention to detail that I am sure has helped so many people - I'll be back, for sure!
It's easy because you are here , thanks Leila for sharing , you are doing so much .
Your channel is really helpful! Really appreciate everything that you do!
Hi Leila, I can't get my filter function in my Excel 365....but this is awesome.... I love it... thanks Leila. you simply the best
I'm grateful that you explained how to use the "fat arrows" to select the table, or columns within the table (I had been having trouble trying to figure out how to do that).
Find thousands solutions in one .. is there any world recognised awared to best teacher...? You simple & amazing 😻😻🙏🙏
Leila, I've been wanting to know how to do this for months now. This was so straight forward!! Thank you so much
Thats a new useful function, instead of combining vlookup and search that i found on google. Unfortunately still using the old version. Kudos to the developer and thanks for the easy explanation. Good job.
Wonderful stuff, Your excel videos stand out in the crowd. Please continue to share this excellent series.
Thanks Leila so much for this video. This is the function I need right now. By the way, I always find the solution from your channel and your lesson on Udemy. Thanks again for doing many great things.
Wow! This is a game changer for me! No more complicated lookup formulas!!
It really is a great new feature.
I HAVE NO WORDS FOR YOUR PRAISE
Your dynamic array course is awesome. Thanks Leila!
I'm glad you like the course.
Thanks for sharing the Table concept with filter. Its very helpful for large dataset.
INSANE, this is SO helpful. I used to write lines and lines of code to get the same results as this function =iferror(index(aggregate(Row..... I wish I had known this video existed earlier. Thank you so much!!
Whenever I have a problem in excel, I generally look for your videos and always found a solution for the problem.
That's great! Glad the videos are helpful.
Great new functionality, I will change almost all of my reporting tools (to answer your question). And as always your presentation is clear, easy to follow, and just right!
Glad it was helpful, Joachim!
Amazing tool of-course from amazing trainer.
This will save me so much time.
Thank you Leila
FILTER and UNIQUE functions are indeed amazing! And your videos are absolutely fantastic too!
Thanks a lot!
Great demo of what will become the 'goto' look-up function in the future.
I'm just loved this new Function! Revolutionary! Thank you for the amazing video!
Glad you enjoyed it!
Thanks Leila. This was really useful and explained with such clarity. You are awesome!
Thank you, Leila! You've solved my problem with your explanation! You're Great!
Much, much easier!! Thank you so much!
My favorite teacher. You made me an advanced exceller at work. Thanks!
I'm glad to hear that :)
I love you I have made like 1000 firms just from your Channel thank you!!!
So extraordinary mam, I think this function is very useful to complete my work faster than before. Thank you for the lesson
Аааааа! "ФИЛЬТР" теперь работает и "умными таблицам", а не только с диапазонами!!!! КРУТО!!!! Вот это по-настоящему мощно!
Thanks sis...Ur teaching is really very good...God bless you..
Hi Leila! Love your channel. You are an excellent teacher. One thing I don't think you mentioned is that Filter does not appear to support partial matches (e.g. Smith*). This is a pretty major omission when compared to DGET or Advanced Filter.
Wow, I just found here the answer that relieved our pain point, thanks a lot. Your demonstration is fantastic, thanks a lot.
Great to hear!
You are a lifesaver, Leila! This helped me so much!
I can’t wait to try this tomorrow morning on my return to work... looks great
Great start in the new year!
Thank you dear Laila for producing such helpful videos. I use if and benefit from it in my office work. Thanks one more time.
You have an awesome command of technical aspects and mentally your coolest speech level, Your videos really inspire us and teach us a lot of new formulas that would be required in the office as well as the job. Keep going like this. keep doing our brainstorming with your knowledge.
Just re-watched, great demo of how to use multiple criteria with table columns using boolean logic.
Thank YOOOOOU! Was waiting for this function:)
It works just like Filter function in DAX. I was always wondering for the day that it will come in Excel. Now it's reality :)
Thanks Leila. FILTER is awesome! Thumbs up!!
Amazing function, quite easy and very useful I see. It will def. find its place in everyday use!
Looks very straightforward to use. Thanks!
This video saved my sanity after a sleepless night of trying to figure this out!! Thank you!!!
Great formula... It's gonna same so much time in day to day work. A big thumbs up 👍
Extremely practical and useful formula, well explained as usual.
Thanks in a million. What a twist with Table Referencing!
I love this. SO WONDERFUL, SO SIMPLE, SO EASY TO FOLLOW, SO EFFECTIVE and I'm out of praise adjectives !!! Thanks for uploading :)
Thanks for all these nice adjectives :)
This function seems much powerful with complex data. Awesome Leila
It really is a great feature.
Extremely useful. I was badly searching for that.
Thanks and regards
Leila & Team: I’ve got Office 365 at home. Later this year we’ll be getting Office 365 at work. Thanks for letting me know of, Ctrl Z and the ‘Oblique arrow’ to select the ranges. I’ll be ahead when we obtain 365 and be able to help more colleagues !
I used to be limited to Excel 2016 and I think I was making workbooks for employees that were running 2013. so I was limited in what I could create, now that everyone is upgraded company wide, my life is easier and no longer limited with the formulas I can us.
Leila, Great lesson! I love dynamic array functions and have wrestled with using them in production environments since gaining access as an Office Insider. On one hand, they're so useful (especially automatic update instead of separate refresh) and easy to use. On the other hand, they're experimental, so implementation runs the risk that Microsoft may change them before final release (as it did with XLOOKUP). Generally, I have avoided including in a production environment, but I can't wait for final release. Of the new dynamic array functions, I've used FILTER, UNIQUE, & SORT/SORTBY most frequently. Also, SUMPRODUCT is finally (change no longer comes easy at my age!) second-nature to me: * is AND, + is OR. Thanks for your teaching!!!
Glad you like it Jim. FILTER and UNIQUE are also my favorites. The DA functions are already being rolled out to general public - one of our "normal" office 365 PCs already got it. The other laptop didn't yet - so we just have to be patient. I doubt these functions will change since they are already being rolled out. XLOOKUP is still in beta though....
Ooohh ... Can’t wait! Thanks for the insight that they’re starting to roll out for general availability.
Question: how can I tell that a DA function is general availability & no longer Insider pre-release?
You can't really unless you have a separate PC that's not in Office Insider.
Understood. Thank you.
Thank you Leila for sharing this, it will make task easier and save time.
Superb Leila. This is what i was looking for. Thanks u really made my life easy. Plz continue this contribution to my life 😊.
Glad it's helpful. I'll do my best :)
This is exactly What I have been looking for
The last one,Finding Division and region together
Glad it's helpful for you.
Very Helpful and well taught ! Thank you !
I'm definitely a new fan as I did not know a lot about Excel. Thanks
very helpful, looked allover for a similar solution, finally found this video, thanks again
The tip on returning lists based on 2 criteria is very helpful.
You are a true gem, Leila! Thank you so much!
You're so welcome!
Mam you are doing great help us by spreading your knowledge
Keep posting these types of learning vedio even there is less likes. Your vedio are simple and best for learning.
Love and respect for your work
Form India👍👍👍👍
Your video is super informative and really helpful! Keep up the awesome work!
Powerful function! Am in love with it
Great presentation! Thank you very much!
Thanks, we can perform this activity through advance filter option as well, given under data tab.
Thanks for the new & amazing things always. 👍
Love this new Filter Function! Thanks!
Me too! It's really great.
Awesome Function! Thank you for explaining every detail about it!
My pleasure!
This is gold! So so brilliant... Loved this function
Glad you like it.
I wish I had known this yesterday..... ;) I'll be trying this out when I get back into work. Thanks Leila.... excellent presentation and very informative as usual... Thanks! :)
Hope it's helpful at work Tim.
Hi Leila Gahrani,
This is exactly what i want. Thank you so much.
Hi Leila!Really like the look of the Filter Function the Syntax seems pretty easy to understand!Thank You for the Awesome Tutorial :):)
It's really easy to implement. Glad you like it.
Leila - this is supercool & comes in handy. Very useful. Thanks much
Thank you very much for this wonderful explanation.
thank u it's really helpful .......watching from Pakistan
amazing, awesome, and very nice... thanks for the video...
Please keep adding more videos like this...
Excellent explanation and guidance as always.
Thanks very much Leila...thanks a lot for beautifully explaining this filter formula..
You're welcome 😊
Hi Leila, thank you so much for your videos, I learned a lot from them!! Small question: in which video you explain how to sum only values (in money value) of a filtered category in a database? I was looking for this but I couldn’t find it in you playlists. Thanks a lot and keep up the good work! 👍👍👍
Outstanding video! Your instruction really helped me understand this new and most useful function (I just upgraded from Office 2016 to Microsoft 365). Thank you!
Great to hear, Douglas!