Find Multiple Match Results in Excel (Easier Solution For ALL Excel versions)
Вставка
- Опубліковано 8 лип 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Finding an Excel solution to get MULTIPLE matches for a lookup value is often causing major headaches. The go-to VLOOKUP formula does not work here because it just returns the first match!
What if you wanted to use VLOOKUP but return ALL matches? You can do that now, with the new FILTER function available in Microsoft 365. But what if you don't have Excel 365? Is there an easy function that can return multiple match results?
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/multiple-ma...
In this tutorial I'll show you a simple(er) approach that solves this challenge in 2 simple steps. It works in all versions of Excel and is quite easy to remember (at least easier than my original Aggregate version here: • Return Multiple Match ... ). Many thanks to Bob Umlas for sending me this solution!
In addition, I'll show you other options you can apply depending on your version of Excel:
- If you have Microsoft 365 you can take advantage of the new FILTER function
- In Excel 2019 you can use the TEXTJOIN function
- For Excel 2010 and higher you can use the AGGREGATE function
This way you have the COMPLETE guide for any situation on how to find multiple matches in your Excel data set.
00:00 How to Get Multiple Matches in Excel
01:17 Easy Solution For All Excel Versions
11:31 Solution with TEXTJOIN & AGGREGATE
11:50 Easiest Solution with FILTER
12:19 Wrap Up
LINK to original video: • Return Multiple Match ...
FILTER: • Excel FILTER Function ...
TEXTJOIN & AGGREGATE: • Return Multiple Match ...
➡️ 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/multiple-matches-complete-file
This Girl is Amazing, your channel helps me to be pro in Excel.
It's just amazing. There's no other channel I go first when I'm looking for a clever solution for an Excel problem. Keep it up!
Great to hear Michael!
Fantastic. I many read web pages & watched videos many examples of MULTIPLE matches, but this is the only one that I could get to work. Very well explained. Thank you.
Leila, you are the sweetest instructor I follow on the internet! Just imagine, how many excel-ninjas you have been created!!! Thank you.
Wow, thank you! We need more Excel Ninjas :)
Brilliant! Thank you Leila for sharing all those variations with us. I learn a lot from you.
I love the new 365! Have been a huge help already for me in my work
You are the best Leila, you went beyond and provided alternatives to people.
Thanks a Lot Leila, Indeed you're great. Stay Blessed
Thank you Robert for dropping by.
Thank you for showing so many different method to accomplish the goal.
A great work Leila. Thank you very much for your explanation!
Love that you can pare down additional criteria by dividing them against themselves in the row component (resolving the DGET limitation on multiples until I can get the XLookup)
Thank you for enlightening me Leila with your cool Excel Tricks!
My pleasure Jobert!
I always refer your videos while I prepare Dashboards.
Thanks for your videos.
I am currently forced to use Excel 2019, and this video came in so handy, like many of your videos. Thank you so much!
Hi Leila, love your videos. They are really helpful. Just want to know, can we obtain the same result in one single cell instead of multiple cells. I had to create a UDF to get multiple value in one cell. I liked Textjoin approach but I faced the issue of duplicate values. Can you help me with a solution for looking up a value may or may not have criteria and returning values in a single cell without duplication
Thanks LG, and Bob, it was really helpful
Thank you so much for your awesome content Leila. I have a question about this formula if I may. Is it possible to adapt it so that it can retrieve a date value "up to and including" the date specified ? So ... instead of entering "GAME" for example, the end-user would type a date, and then all values matching that date (or *older* than that date) would be returned ? Thank you so much =]
Leila, Thanks for this nice approach, really good.
You definitely know your stuff. I enjoy rescuing myself here. I use almost everything there's in excel in my line of work.
Glad to hear it!
Hi Leila, do you have a solution that would work with data validation? so far, i had no luck when the data is structured the way you have it in this video. thanks!
thank you for this tutorial. I was just wondering if it is possible to use textjoin function together with this so the return values are in one cell?
Thanks leila this is very helpful. Makes my life easier.
Thanks Leila for sharing and for all the great videos you post. Always enjoy your insight. Stay safe excel goddess. 👩💻
Exactly what I needed !!!!!
This is good stuff. Thanks very much Leila.
These are great methods to solve a common problem I have. Thank you! Your explanations are always very clear!
Happy to help, Simon!
Thanks Leila. Bob is the Master!
He sure is.
Extremely helpful Thank You
Hi there, This has worked really well apart from one issue. When i reach the end of the matches, the function starts re-counting from cell 1 so I end up with the same list of data duplicated over and over with one empty cell in between each data set. Any idea why this is happening? Thanks
Ghie
Thanks!
Do you know of a formula that would allow you to average the multiple match results? For example, if you wanted to find the average revenue of all apps in the "Game" division?
This is awesome, thank you so much!
Great explanation. Thank you.
Thank you! exactly what I needed. I am using office 2019...any way to put a filter and sorting on the resulting data without breaking the formula? I suppose sorting the original data set first would work too but was hoping there was a way.
That was amazing. Thank you.
Hi Leila, I always look for your videos when I look to learn something new. I tried this and it almost provides the results I need. Where I am lost is rather than vertical results, how can I get them horizontally?
You are a great teacher.Your way of of explaining is easy going and simple to understand.Thanks a lot for such awesome videos.👍👍🙂
It's my pleasure
awsome video!
Thank you very much, from Germany
Great tips thank you
Thanks for you and Bob, you are doing much to the world.
Our pleasure :)
Hi Leila every time I watch different Video and find something more interesting in Excel thank you.
Happy to hear that, Mohammad!
Very interesting and useful video. Thank you...
Greetings and blessings, I would like to tell you personally that I have actually become professional in Excel thank you so much for the videos
Just too good Leila. Wonderfully explained. Your Channel and Mike Girvins Channel are the two very often watched channels by me. Amazed at how wonderfully you people do it. Continue the good work to help us. Just one question on the Offset function. What will be an example of offset function in which multiple columns and multiple width may be used ? Thanks once again for all the knowledge shared !
I eagerly waiting for your Excel videos... in every single video I learn new things... thanks 🥰
I'm happy to hear that Ashwin!
Solution vraiment géniale et très utile, merci encore pour tes effores Leila...❤
Glad you liked our formula marathon :)
thanks a lot, i really appreciate your effort
Omg you saved my life. Thank you so much
Leila, is there a way to put these values in a drop down list instead of just showing them in a table? Thanks!
OMG Leila, You are the most beautiful equation.
thank you so much ,for giving a valuable information
Excellent Leila you rule! , God bless you more
Great Video!!! Thank you!
I applied it to my cash/bank control sheet to generate vendors' statement of account and it worked perfectly fine, BIG thanks you Leila.
Nice work!
Dear Leila, thank you for your extremely helpful and concise content. May i know how can i combine "Reverse Index & Match" with "Find Multiple Match" results? Extremely grateful if you could help. Thank you.
Thanks a lot Leila and Bob for the solution 😊
Our pleasure :)
Hi Leila, I can’t locate “Manage” option in excel 2013 where I can make connection and see the tables in diagram view. Does 2013 have it?
We know F4 for cell reference, but it can also be used to repeat the same action we have just applied
Very well explained
Great to learn from you. it is very rare results required.
Thanks and respect TEACHER , Your explanation is great ,Excel easier than before
Glad you think so!
Hi your videos are great.
Can you help me to know when we can save a set of data, in our case
We have product name and its code.
What I'm looking for solution is ,When we type first letter recommendation should come and then when select product from list in next column its code should come.
Like to know Whether we can do this
You are the best of the best
All time I found solution with you
Many thanks
Seven differ approaches for finding multiple matches explained in less than 13 minutes. Truly amazing.
Can't wait till the day all of us can share in your excitement for the utopia that is the Filter function.
Always good to have options. Hopefully soon utopia will be around the corner for you :)
Hi Leila,
Love your videos, its easy to follow along.
I have a question, wishing you could provide some support.
I have a workbook with around 20-30 data connection that grabs data from website. Having auto refresh on all these connection would take a long time and some arent need on certain days.
Is there a way to code in VBA to refresh data connection based on cell value? i.e. I have LAX data connection name and in cell B1, I enter LAX and click refresh, the data connection will refresh that particular connection. Is this possible?
Thanks you
great work
Thanks Leila, filter function is amazing 👍🏼
It sure is Stephen!
Thankfully, there is the FILTER Function now... saves a lot of effort and time to obtain many results, including the multiple matching. 👌
So true! The new dynamic arrays make it much easier now.
Amazing video Leila. Frankly it is too much for many including me. One has to watch your videos on Index & Match and Offset before proceeding to this video.
True. The formulas are short but the concept is more complex....
Thank you soo much 👍👍
This is really useful if you are creating a workbook that you know is going to be used by people with an older version of Excel. Having said that, you do get those awkward people who refuse to upgrade their software, so you are forced to use more convoluted methods to achieve what could be done a lot more easily using dynamic array functions!
True Andrew. We'll have to deal with compatibility issues for a while.
Incredible!! Thanks a lot!!!
Glad you liked it!
How would you change the formulas in column F if you wanted to look up two Apps for Division? So for example if you wanted to look up Game and Utility and compile them into one list?
hello, I am from India..your lessons are helpful , thanks .
II like simplicity these simple formula's are easy to know, thank you
Glad you like them!
Thank you so much for the formula. It worked great on my golf roster checklist. Is their a reason that I can’t sort the results? I am using Excel for Mac 2010.
Hi Leila, do you have any video on a simplified Monte Carlo Simulation model ?
Your page is booming baby girl ...You have close to 2 million subscribers ....I know them youtube checks 🔥🔥🔥🔥
Really helpful
Genius approach ! Leila Fairy
Thanks for the share.
You are a God Gifted. 😍
So beautifully explained by the queen of Excel 😊
Thank you Peter! 🙂
Thanks Leila AND Bob!
Our pleasure :)
Thanks a Lot Leila
like the logic way you are teaching to us , easy to understand and put into practice
Glad to hear that.
Thanks for sharing!
My pleasure Malina. Very nice picture :)
Leila I used your formula taking data from another workbook sheet. Its works fine only when the source workbook is open. What do i need to do to solve this? thanks in advance Ian
Hi Leila!
Your each video is very very professionally useful.
Here I would request to hear from you, will it work if our data lies on different sheets or workbooks and required to fetch all matches in different sheets or workbooks.
Thanks n Regards,
Interested content for the next video:
Hello Leila, Can you guide us plotting ternary diagram in Excel ?
Thanks in advance. I have learnt many useful techniques from your video sessions.
This one really good. i was wondering is there a way to find all multiple values without changing the heade(here Game is the header, if i want the same for utility in need to change the header to utility. can we do this automatically by making any changes to the formula @leila
Amazing solution Leila very easy to apply in routine work.
Glad you think so Jimmy!
Hi Leila, This is really awesome.. Wondering, if in the Game example, I need one more filter say year as 2010 & 2011, and I need to pull only for 2011. Can you give some idea on it on how it can done?
Hey did you find any solution for this problem, please let me know
Thanks a lot Leila this one really helpful in lot of reports...🥰😇👍👏
Glad to help.
How would you do this if you want the results in columns instead of rows? I've watched your "find multiple matches & Dependent Drop Down lists" video and it works but I would like to do it this way. I use strutured tables for the lookups. Also, can you skip a column where the results will go. I need to put placeholders where I need to do another lookup. Your videos are amazing! I have learned so much from watching them!
Well explained
Hi Lelia - Great video. Question; If I enter multiple ID numbers in one cell separated by comma or by line, I want to return all the names of those people together in another cell. Can this be done?
Thanks!
Thanks alot for this awesomeness, one step furthuer, can you sugguest how add another condition same as this in a different columbs
Well Done Leila