Excel Lookup to Return Multiple Values with FILTER Function
Рет қаралды 672,833
Күн бұрын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
@LeilaGharani 3 ай бұрын
Grab the file I used in the video from here 👉 pages.xelplus.com/filter-file
@rakirada4280 8 ай бұрын
Why can't others explain it the way you do. You are making complex functions feels really simple. Thank you!
@unforgetable4747 3 жыл бұрын
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.
@hakabdul786 4 жыл бұрын
Complexity of any functionality becomes simple with your lucid explanation ! Keep up the good work👌
@deesquare08 2 жыл бұрын
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.
@babytoy2333 Жыл бұрын
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.
@richardhay645 4 жыл бұрын
I have used it regularly since November 2018. It's easy, useful and efficient. I use Adv Filter occasionally, but usually FILTER.
@gabosdad4007 3 жыл бұрын
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!
@neilgore 2 жыл бұрын
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.
@TheMulvsInOz 2 жыл бұрын
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!
@rina1592 3 жыл бұрын
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.
@greywolf4454 4 жыл бұрын
A great method to make a dedicated sheet to search big data tables. Thank you Layla for the clear explanation.
@mark91345 10 ай бұрын
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).
@nguyenTran-ju7lp 3 жыл бұрын
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
@CptPilot 3 жыл бұрын
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
@richardhagans1644 Жыл бұрын
This is amazing, easy to pull data view of a master table! And great tips and tricks as always, Leila.
@benbowyang 3 жыл бұрын
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.
@charlesgrace6729 2 жыл бұрын
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.
@levkani 4 жыл бұрын
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
@ManthaarJanyaro 4 жыл бұрын
Teacher of the year...
@keithdutch5295 Жыл бұрын
Leila, I've been wanting to know how to do this for months now. This was so straight forward!! Thank you so much
@guardianjuan 4 жыл бұрын
Slowly your channel is becoming one of my favorites, seriously I always get happy when I see a new video.
@LeilaGharani 4 жыл бұрын
That's great! I'm glad to hear that.
@michebernier5298 3 жыл бұрын
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 !
@LeilaGharani 3 жыл бұрын
My pleasure. I'm glad it's helpful.
@dennisd5776 4 жыл бұрын
Great demo of what will become the 'goto' look-up function in the future.
@ananguyen4509 4 жыл бұрын
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.
@mariogodoy5706 3 жыл бұрын
Thank you, Leila! You've solved my problem with your explanation! You're Great!
@malithfernando7091 4 жыл бұрын
Find thousands solutions in one .. is there any world recognised awared to best teacher...? You simple & amazing 😻😻🙏🙏
@JitendraMourya-wo2lv 28 күн бұрын
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.
@orizo5330 4 жыл бұрын
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.
@bandar2032 4 жыл бұрын
Amazing tool of-course from amazing trainer. This will save me so much time. Thank you Leila
@jurlc7826 Жыл бұрын
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! 👍👍👍
@sunilsingh10 4 жыл бұрын
Wonderful stuff, Your excel videos stand out in the crowd. Please continue to share this excellent series.
@excelisfun 4 жыл бұрын
LOVE the Lavender Filter Visual : ) : ) It is very easy, and fun!!!
@LeilaGharani 4 жыл бұрын
Thank you Mike. It definitely is fun!
@tha2irtalib343 4 жыл бұрын
It's easy because you are here , thanks Leila for sharing , you are doing so much .
@OhiAlofokhai 3 жыл бұрын
This may well be one of the best functions yet as it provides multiple solutions and is easy to use. Thanks Leila!
@LeilaGharani 3 жыл бұрын
It definitely is one of my favorite functions!
@raginirani9 2 жыл бұрын
Thanks for sharing the Table concept with filter. Its very helpful for large dataset.
@stephenstephen8660 4 жыл бұрын
Thanks Leila. This was really useful and explained with such clarity. You are awesome!
@doorveshsantbakshsing9607 3 жыл бұрын
Your channel is really helpful! Really appreciate everything that you do!
@zjweine 4 жыл бұрын
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?
@impex2324 3 жыл бұрын
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!
@LeilaGharani 3 жыл бұрын
Glad it was helpful, Joachim!
@bobbirowlett6728 7 ай бұрын
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?
@hanhat4660 8 ай бұрын
Your video is super informative and really helpful! Keep up the awesome work!
@graemegourlay2850 3 жыл бұрын
Just re-watched, great demo of how to use multiple criteria with table columns using boolean logic.
@aristoalexius 4 жыл бұрын
So extraordinary mam, I think this function is very useful to complete my work faster than before. Thank you for the lesson
@tannertucker22 4 жыл бұрын
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
@eduardov1452 Жыл бұрын
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!!
@shabbirgillani9607 4 жыл бұрын
Thanks, we can perform this activity through advance filter option as well, given under data tab.
@ismailmasoom5007 10 ай бұрын
Thank you dear Laila for producing such helpful videos. I use if and benefit from it in my office work. Thanks one more time.
@FabianSerlik 3 жыл бұрын
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,
@Grumpy_Granddad 4 жыл бұрын
I can’t wait to try this tomorrow morning on my return to work... looks great
@mirrrvelll5164 4 жыл бұрын
Amazing function, quite easy and very useful I see. It will def. find its place in everyday use!
@dadministrator2195 Жыл бұрын
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
@Brushoi 4 жыл бұрын
I'm just loved this new Function! Revolutionary! Thank you for the amazing video!
@LeilaGharani 4 жыл бұрын
Glad you enjoyed it!
@subratadutta4562 4 жыл бұрын
Thank you Leila for sharing this, it will make task easier and save time.
@yulinliu850 4 жыл бұрын
Your dynamic array course is awesome. Thanks Leila!
@LeilaGharani 4 жыл бұрын
I'm glad you like the course.
@balu9999999999 4 жыл бұрын
Whenever I have a problem in excel, I generally look for your videos and always found a solution for the problem.
@LeilaGharani 4 жыл бұрын
That's great! Glad the videos are helpful.
@joelbeauchamp6687 4 жыл бұрын
Much, much easier!! Thank you so much!
@nezumi_za 2 жыл бұрын
You are a lifesaver, Leila! This helped me so much!
@AR-rs2dy 3 жыл бұрын
The tip on returning lists based on 2 criteria is very helpful.
@captvo 3 жыл бұрын
FILTER and UNIQUE functions are indeed amazing! And your videos are absolutely fantastic too!
@LeilaGharani 3 жыл бұрын
Thanks a lot!
@gintomino4136 4 жыл бұрын
My favorite teacher. You made me an advanced exceller at work. Thanks!
@LeilaGharani 4 жыл бұрын
I'm glad to hear that :)
@sumij4233 3 жыл бұрын
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?
@akemp06 3 жыл бұрын
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 !
@janithrukshan 4 жыл бұрын
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.
@clumsygreyhoundgaming-powe3388 4 жыл бұрын
I love you I have made like 1000 firms just from your Channel thank you!!!
@al7385 3 жыл бұрын
Extremely practical and useful formula, well explained as usual.
@reneedembowski5226 3 жыл бұрын
Thank you for your very easy to follow instructions. How would I make it that only one value (not the whole line) populates? So for example, I only want the row 'utility' to populate, not the whole line...
@XCroxousX 2 жыл бұрын
This video saved my sanity after a sleepless night of trying to figure this out!! Thank you!!!
@garrettmorgan6835 4 жыл бұрын
Wow! This is a game changer for me! No more complicated lookup formulas!!
@LeilaGharani 4 жыл бұрын
It really is a great new feature.
@pedrocardoso328 3 жыл бұрын
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?
@kozaklogisticpvtltd8856 4 жыл бұрын
I HAVE NO WORDS FOR YOUR PRAISE
@srikanthravada 2 жыл бұрын
Great formula... It's gonna same so much time in day to day work. A big thumbs up 👍
@kamtaprasadgupta6711 3 жыл бұрын
very helpful, looked allover for a similar solution, finally found this video, thanks again
@davidroper2549 4 жыл бұрын
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 !
@suleimanshaik 2 жыл бұрын
I'm definitely a new fan as I did not know a lot about Excel. Thanks
@jimfitch 4 жыл бұрын
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!!!
@LeilaGharani 4 жыл бұрын
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....
@jimfitch 4 жыл бұрын
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?
@LeilaGharani 4 жыл бұрын
You can't really unless you have a separate PC that's not in Office Insider.
@jimfitch 4 жыл бұрын
Understood. Thank you.
@graemegourlay2850 4 жыл бұрын
Nice tip with the boolean logic syntax, I was trying to use the AND and OR functions and it wasn't working
@Princia_dance123 Жыл бұрын
Thanks sis...Ur teaching is really very good...God bless you..
@vinodjindal4354 4 жыл бұрын
Extremely useful. I was badly searching for that. Thanks and regards
@petecardona8203 Жыл бұрын
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!
@ltcmdc1782 Жыл бұрын
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.
@kuishikama348 3 жыл бұрын
Nice video and a very helpful function. I just have a question: I build a link in the original table, use the filter function, and in the filtered output (spill) the hyperlinks are replaced by just the readable text. It basically lost the hyperlink in the process. I had similar behaviour using PivotTables. Any idea why this happens and how do retain the links during filtering?
@kumarsujeetshaw 4 жыл бұрын
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👍👍👍👍
@mtroanoke 4 жыл бұрын
Looks very straightforward to use. Thanks!
@janbotha7160 4 жыл бұрын
Thank YOOOOOU! Was waiting for this function:)
@johng5295 3 жыл бұрын
Thanks in a million. What a twist with Table Referencing!
@user-rj3qe6hn6n 4 жыл бұрын
This is exactly What I have been looking for The last one,Finding Division and region together
@LeilaGharani 4 жыл бұрын
Glad it's helpful for you.
@kalentman 4 жыл бұрын
Hello, well done for these useful videos you make. If we set that "FILTER" function isn't available wright now, is there any other combination (functions) that makes the same think? Thank you!
@enochgodson53 3 жыл бұрын
Cool function, thanks for the tutorial. I am unable to figure out how to filter a table from a different sheet.
@unicorngreen 4 жыл бұрын
Awesome explanation, looks so simple!! But what if FILTER function is missing in my Excel version?
@mohamedsaid7514 4 жыл бұрын
Thank you very much for this wonderful explanation.
@ashleshanatu1317 3 жыл бұрын
That is quite useful... Thanks for sharing this. One question.... How many filter parameters can you include in the formula?
@romualdephraimbeda1736 4 жыл бұрын
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 :)
@Monther.Matrok 4 жыл бұрын
Great lesson, fantastic I really like this ❤️ I have one question Is * different from function And()
@javedahmed8343 2 жыл бұрын
You made us learn more than filter items the use of * operator as I thought u may have to use "and " there.
@do_you_know_global_varieties Жыл бұрын
Hi Leila Gahrani, This is exactly what i want. Thank you so much.
@sachinrv1 4 жыл бұрын
I love this. SO WONDERFUL, SO SIMPLE, SO EASY TO FOLLOW, SO EFFECTIVE and I'm out of praise adjectives !!! Thanks for uploading :)
@LeilaGharani 4 жыл бұрын
Thanks for all these nice adjectives :)
@anonje 8 ай бұрын
Great work . What about top 10 Sales product and lower 10 ? we need a formula for that .
@hermanmafo4599 Жыл бұрын
Would really love to compare filter function with index & match function. You're great teacher!
@ianmatthews7385 5 ай бұрын
Filter is far superior by the look of it
@michebernier5298 3 жыл бұрын
Very Helpful and well taught ! Thank you !
Leila Gharani
Рет қаралды 511 М.
Leila Gharani
Рет қаралды 46 М.