How to Use the Excel FILTER Function - Lookup to Return Multiple Values
ฝัง
- เผยแพร่เมื่อ 5 ก.พ. 2025
- 🔥 400,000+ professionals trust our courses-start your journey here 👉 link.xelplus.c...
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....
✨ 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 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.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩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
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.
Why can't others explain it the way you do. You are making complex functions feels really simple. Thank you!
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!
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.
I am the foreign learner form Hong Kong. It is easy to understand that how to use those function to check it out with several examples! Thus, Leila's presentation style with soft, smooth speed with clearly description in subtitles, which is enhancing the understanding during the process. I always prepared it well before my interview and daily usage. Big thanks for your video!😄☺
Complexity of any functionality becomes simple with your lucid explanation ! Keep up the good work👌
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.
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.
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.
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.
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.
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.
Table1: A1 has Channel IDs B1 has emails both repeat as every email might belong to multiple Channel IDs.
Table2: has a subset list of Channel IDs specific to a team.
I need to look at the Channel IDs in Table2 and pull a list of emails who associate with those Channel IDs
What is the best way?
I like excel, and I like excel more when I watch your videos. I could listen to you all day....
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!
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
LOVE the Lavender Filter Visual : ) : ) It is very easy, and fun!!!
Thank you Mike. It definitely is fun!
Teacher of the year...
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!
Wow! This is a game changer for me! No more complicated lookup formulas!!
It really is a great new feature.
This video saved my sanity after a sleepless night of trying to figure this out!! Thank you!!!
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.
It's easy because you are here , thanks Leila for sharing , you are doing so much .
This is amazing, easy to pull data view of a master table! And great tips and tricks as always, Leila.
I felt it will be rude if I go out from this valuable video without subscribing, clicking like and saying many thanks to you Leila.
Thanks for subbing!
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!!
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!
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.
Thank you dear Laila for producing such helpful videos. I use if and benefit from it in my office work. Thanks one more time.
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).
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.
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
Leila, I've been wanting to know how to do this for months now. This was so straight forward!! Thank you so much
Thanks for sharing the Table concept with filter. Its very helpful for large dataset.
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.
I have your video. Although I have not tried it yet, It appears to be a very helpful function. No doubt I will probably walk through your video I few more times as I build my spreadsheet. You are a good teacher.
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.
This function seems much powerful with complex data. Awesome Leila
It really is a great feature.
My favorite teacher. You made me an advanced exceller at work. Thanks!
I'm glad to hear that :)
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!
The tip on returning lists based on 2 criteria is very helpful.
A great method to make a dedicated sheet to search big data tables. Thank you Layla for the clear explanation.
FILTER and UNIQUE functions are indeed amazing! And your videos are absolutely fantastic too!
Thanks a lot!
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.
Amazing tool of-course from amazing trainer.
This will save me so much time.
Thank you Leila
Liebe Leila, Filter Funktion ist absolut großartig, Viele Liebe Grüsse 🤗
I am loving it too Katerina :)
Find thousands solutions in one .. is there any world recognised awared to best teacher...? You simple & amazing 😻😻🙏🙏
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.
Your channel is really helpful! Really appreciate everything that you do!
yes it is to use. I even use it inside transpose function
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
Your dynamic array course is awesome. Thanks Leila!
I'm glad you like the course.
OMG thanks so much. This was exactly what I was looking for. You helped me solve this task from work where I have data on over 60000 rows. Without this tutorial I would have been lost!
Glad to help :)
this filter formula not recognizing. which ver of MS office does support this formula.?
Only in Office 365.
Great formula... It's gonna same so much time in day to day work. A big thumbs up 👍
I HAVE NO WORDS FOR YOUR PRAISE
8:00 If you do the logical maths the * and + become very obvious. Great video. Very helpful.
Glad you find the tutorial helpful.
Simply an excellent explanation!. Thank you for sharing! I have a question, how do I create a filter & large formula for, let's say returning two criteria like the top 10 selling items for a particular subcategory. I want it to return, the particular subject (from a data-val list or not) and the top 10 selling items. I know I can do it in a Pivot table easily, but I'd like to think outside the box and learn it this way. Thank you again!
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 :)
You are a lifesaver, Leila! This helped me so much!
I have used it regularly since November 2018. It's easy, useful and efficient. I use Adv Filter occasionally, but usually FILTER.
I'm definitely a new fan as I did not know a lot about Excel. Thanks
Great tutorial! I have a dought: What if we are looking for text within a cell, usually we use "*"&cell&"*" in the MATCH function. How do we get multiple results using this feature?
Аааааа! "ФИЛЬТР" теперь работает и "умными таблицам", а не только с диапазонами!!!! КРУТО!!!! Вот это по-настоящему мощно!
Great Help & I resolved my issue once data was Copied to Worksheet where "Filter" function was present. Can this be used to "Filter" Data in a new Worksheet to present Clean View without Hiding Data / Columns?
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👍👍👍👍
I love you I have made like 1000 firms just from your Channel thank you!!!
I just found your channel. This is awesome, and BONUS, Excel for MAC, is an Office 365 derivative, so Mac Users have Filter as well.
Happy New Year!!!
Glad it helped, Anthony. Happy New Year!
Hello Leila, thank you very much for your great video. I have an additional question to go further: how to use a wildcar "*" in the criteria field and return a relevant filtered list, please?
Thanks Leila. This was really useful and explained with such clarity. You are awesome!
Thanks sis...Ur teaching is really very good...God bless you..
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 :)
You made us learn more than filter items the use of * operator as I thought u may have to use "and " there.
Wow thanks. I have not used the Filter function before. That looks easy to use. I have a table of data which shows every time a function has been attempted for a customer with the Customer ID, Channel (which is determines whether it was completed by frontline staff versus a back office team), Product And Status (Pass, Fail or Error). Is there formula I can use that will determine whether a Fail Status for a given Customer ID and Product received a sub-sequent Pass status in a later row for a particular Channel?
This is exactly What I have been looking for
The last one,Finding Division and region together
Glad it's helpful for you.
Thanks for sharing Leila :) I think it's a really cool function!! it eliminates the need for complicated formula's
It really is!
This is great. Is there a way to select all List values together and get the corresponding data at once, or does this need to be done one at a time ?
Just re-watched, great demo of how to use multiple criteria with table columns using boolean logic.
Excellent video, as usual.
I'm finding the Filter function does not work with my table; the table has dynamic content and the function's Include matches will not find the values unless I hard-code them in the table.
Suggestions?
Hi thank you for your videos. I like them a lot and learn too much from your videos. I used the Trasparent forumla and use my source from one sheet to an other sheet, but same work book. One of the column heads has no value but the transparent is showing a value for that heading and I don't know where it is bringing that value from. Can you please make a video on this or tell me in reply how to locate the error and fix it?
Hi Leila - Thank you for this video. I had no idea how to do this. I would also love to have you explain how to sum only values (in money value) of the filtered category returned, rather than displaying the lines of detail. Is that an easy addition to the formula?
I luv how u explain things very professional and methodical using various scenarios. Very attractive, articulate....how about dinner👀👌
amazing, awesome, and very nice... thanks for the video...
Please keep adding more videos like this...
I purchased the course in anticipation that excel new will be released to the masses soon. Waiting is hard knowing that it’s out there.
The rollout already started. One of our office machines already has it, another notebook does not. Make sure you are on the monthly update channel (not semi-annual).
@leila. This morning my Office 365 updated at work. Immediately tested in excel if I have the Data Array functionality... and yes, I do. After kids went to bed, I started following your course and went through all the 7 new functions FILTER, SORT , etc. It works great, you present great, I am impressed and excited to put some to work. Then I wanted to test the XLOOKUP that has been buzzed about and it’s not in yet ! Are there several waves of Office/excel releases?
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!
Hi Leila, thank you very much for you sharing, may i know is it possible for us to do filtering across worksheet? what i mean is in the same excel workbook, I have created several worksheet, i have added my raw data on one, but hope to do the filtering on another worksheet. thank you!
This is fantastic!. You do a good job of explaining your solution. What if I want to pull multiple items one below the other based on a list.
Wonderful stuff, Your excel videos stand out in the crowd. Please continue to share this excellent series.
Thanks for the video's a have learned a lot from them with not have any previous training on excel. I am having an issue that I can not find an answer to. There is probably an easier way of doing this but again no previous training. Where is the best place to ask it since it's not easy for me to explain?
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 !
You have answered my doubt, which would have otherwise remained unanswered if this video had not existed.
Glad to hear that! What was your doubt?
@@LeilaGharani My doubt was whether we can apply multiple conditions in filter function or not.
This is what I was looking for. Thanks a lot. But what can be done to bring selected columns and not entire table?
Well demonstrated.
What would I need to do to be able to use this to filter results but keep there formatting like text with a strikethrough? For example, I have a list of tasks. Some of the items are completed on the list, and to mark as complete the task receives a strikethrough. Using this function would allow me to filter the list. However, it doesn't show that the text is striken. What am I missing?
I am searching for this function to help me in my job.
Thanks a lot 🙏🙏. I am excited to do it tomorrow 🤩🤩🤩
My pleasure. Hope it will be helpful at work :)
@@LeilaGharani
Thanks Dr. But I found my version is 2010 which is not found but I have another version which is 365 proplus and it was not found also. I don't know why
Make sure your 365 Pro Plus is set to the monthly update channel (not semi-annual).
Thanks a lot 😊😊👍👍👍
Thanks for the great explanation, what if there is a picture in the table?
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! 👍👍👍
Would really love to compare filter function with index & match function. You're great teacher!
Filter is far superior by the look of it
Really great function, what if I need to filter a specific column based on a list of data or a named range where it could change it's values from time to time ?
Or lets say: we want to filter a column based on one of many values in a list which is not logic to apply the OR or AND logical.
Amazing video! What if I would like to get the results of North America, South America and Asia all underneath each other? Any tips?
Great tutorial! If I may ask one question? Is it possible to filter (or lookup) a range of cells/values instead of one or even two values like shown in your tutorial?