Excel FILTER Function - Lookup to Return Multiple Values

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.

✨ 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

🚩Let’s connect on social:

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

[hakabdul]

Complexity of any functionality becomes simple with your lucid explanation ! Keep up the good work👌

[unforgetable]

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.

[deesquare]

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.

[richardhagans]

This is amazing, easy to pull data view of a master table! And great tips and tricks as always, Leila.

[babytoy]

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.

[CptPilot]

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

[excelisfun]

LOVE the Lavender Filter Visual : ) : ) It is very easy, and fun!!!

[rina]

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.

[gabosdad]

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!

[guardianjuan]

Slowly your channel is becoming one of my favorites, seriously I always get happy when I see a new video.

[sunilsingh]

Wonderful stuff, Your excel videos stand out in the crowd. Please continue to share this excellent series.

[nguyenTran-julp]

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

[TheMulvsInOz]

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!

[greywolf]

A great method to make a dedicated sheet to search big data tables. Thank you Layla for the clear explanation.

[doorveshsantbakshsing]

Your channel is really helpful! Really appreciate everything that you do!

[bandar]

Amazing tool of-course from amazing trainer.
This will save me so much time.

Thank you Leila

[keithdutch]

Leila, I've been wanting to know how to do this for months now. This was so straight forward!! Thank you so much <3

[stephenstephen]

Thanks Leila. This was really useful and explained with such clarity. You are awesome!

[OhiAlofokhai]

This may well be one of the best functions yet as it provides multiple solutions and is easy to use. Thanks Leila!

[rakirada]

Why can't others explain it the way you do. You are making complex functions feels really simple. Thank you!