Excel Lookup to Return Multiple Values with FILTER Function
ฝัง
- เผยแพร่เมื่อ 15 พ.ค. 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
Complexity of any functionality becomes simple with your lucid explanation ! Keep up the good work👌
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.
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'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.
Why can't others explain it the way you do. You are making complex functions feels really simple. Thank you!
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.
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!
Teacher of the year...
This is amazing, easy to pull data view of a master table! And great tips and tricks as always, Leila.
A great method to make a dedicated sheet to search big data tables. Thank you Layla for the clear explanation.
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.
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
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.
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.
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).
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!
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
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 have used it regularly since November 2018. It's easy, useful and efficient. I use Adv Filter occasionally, but usually FILTER.
Leila, I've been wanting to know how to do this for months now. This was so straight forward!! Thank you so much
LOVE the Lavender Filter Visual : ) : ) It is very easy, and fun!!!
Thank you Mike. It definitely is fun!
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
It's easy because you are here , thanks Leila for sharing , you are doing so much .
Find thousands solutions in one .. is there any world recognised awared to best teacher...? You simple & amazing 😻😻🙏🙏
Great demo of what will become the 'goto' look-up function in the future.
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.
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!
Wonderful stuff, Your excel videos stand out in the crowd. Please continue to share this excellent series.
Amazing tool of-course from amazing trainer.
This will save me so much time.
Thank you Leila
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.
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.
Thank you, Leila! You've solved my problem with your explanation! You're Great!
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! 👍👍👍
Thanks for sharing the Table concept with filter. Its very helpful for large dataset.
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.
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.
I can’t wait to try this tomorrow morning on my return to work... looks great
Your channel is really helpful! Really appreciate everything that you do!
Thanks Leila. This was really useful and explained with such clarity. You are awesome!
The tip on returning lists based on 2 criteria is very helpful.
Just re-watched, great demo of how to use multiple criteria with table columns using boolean logic.
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!
Your dynamic array course is awesome. Thanks Leila!
I'm glad you like the course.
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?
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?
Your video is super informative and really helpful! Keep up the awesome work!
You are a lifesaver, Leila! This helped me so much!
This video saved my sanity after a sleepless night of trying to figure this out!! Thank you!!!
Wow! This is a game changer for me! No more complicated lookup formulas!!
It really is a great new feature.
I'm just loved this new Function! Revolutionary! Thank you for the amazing video!
Glad you enjoyed it!
FILTER and UNIQUE functions are indeed amazing! And your videos are absolutely fantastic too!
Thanks a lot!
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 definitely a new fan as I did not know a lot about Excel. Thanks
I love you I have made like 1000 firms just from your Channel thank you!!!
My favorite teacher. You made me an advanced exceller at work. Thanks!
I'm glad to hear that :)
Much, much easier!! Thank you so much!
So extraordinary mam, I think this function is very useful to complete my work faster than before. Thank you for the lesson
I HAVE NO WORDS FOR YOUR PRAISE
Great formula... It's gonna same so much time in day to day work. A big thumbs up 👍
Thanks in a million. What a twist with Table Referencing!
Extremely practical and useful formula, well explained as usual.
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 !
Always excellent. Do you have any advice on updating code when inserting adding and deleting columns? Is there a way to make the cell / range references dynamic in the code? Thank you
Thank you very much for a great series of videos. I like many people used Vlookup and other functions to analyse data and tables, but the use of this filter function has made my life easier and it’s far more productive. One of the things I had created was a complex set of logic to use in the criteria in a Filter function; however, I found that even though I created the text of a complex formula I couldn’t get it to calculate the formulas result because it is text. Is there a way converting text into a formula that you can evaluate? Preferably without the use of macros or custom functions. Ideally something like the reverse function of Formultext function
Amazing function, quite easy and very useful I see. It will def. find its place in everyday use!
Thanks Leila. FILTER is awesome! Thumbs up!!
Thank you Leila for sharing this, it will make task easier and save time.
very helpful, looked allover for a similar solution, finally found this video, thanks again
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.
Extremely useful. I was badly searching for that.
Thanks and regards
Looks very straightforward to use. Thanks!
Thank YOOOOOU! Was waiting for this function:)
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?
Thanks, we can perform this activity through advance filter option as well, given under data tab.
Thanks sis...Ur teaching is really very good...God bless you..
Thank you. All your videos are very clear and helpful. Just one question, let's suppose I want only one column to be shown from all the data set. For example, in this case, all the division from the Region, how can I restrict the output to be only one column?
Than you,
Hi Leila Gahrani,
This is exactly what i want. Thank you so much.
You are a true gem, Leila! Thank you so much!
You're so welcome!
This is exactly What I have been looking for
The last one,Finding Division and region together
Glad it's helpful for you.
This function seems much powerful with complex data. Awesome Leila
It really is a great feature.
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.
Would really love to compare filter function with index & match function. You're great teacher!
Filter is far superior by the look of it
Very Helpful and well taught ! Thank you !
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 :)
Thank you for this great video. I'm wondering whether Microsoft could enable us to use this FILTER formula with formatting feature. This would help us to filter a large sets of data/table.
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 :)
Thank you so much for all these courses !! Really appreciate it. I do have one question, if i manage to add weblinks to one of the dataset, would it reflect in the after i filter?
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 :)
Excellent explanation and guidance as always.
Leila - this is supercool & comes in handy. Very useful. Thanks much
Great start in the new year!
Thanks for the new & amazing things always. 👍
Great work . What about top 10 Sales product and lower 10 ? we need a formula for that .
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👍👍👍👍
Great videos and very good explained even complex topics ! I have also a question ! I have now a list of items and I gather a filter result that returns one or more lines and I want to show the result of the filter result as a contained list so it fits my one row in the first table. Any solution to that ? as the results vary I can not past the filter function per row !
Powerful function! Am in love with it
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...