Find Multiple Match Results in Excel (Easier Solution For ALL Excel versions)
HTML-код
- Опубликовано: 20 июл 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
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.
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!
This Girl is Amazing, your channel helps me to be pro in Excel.
Brilliant! Thank you Leila for sharing all those variations with us. I learn a lot from 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 :)
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)
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 !
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
You are the best Leila, you went beyond and provided alternatives to people.
I love the new 365! Have been a huge help already for me in my work
A great work Leila. Thank you very much for your explanation!
Thank you for showing so many different method to accomplish the goal.
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 =]
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, Indeed you're great. Stay Blessed
Thank you Robert for dropping by.
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?
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 this works like magic!
Leila, Thanks for this nice approach, really good.
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?
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?
These are great methods to solve a common problem I have. Thank you! Your explanations are always very clear!
Happy to help, Simon!
We know F4 for cell reference, but it can also be used to repeat the same action we have just applied
I always refer your videos while I prepare Dashboards.
Thanks for your videos.
Thank you for enlightening me Leila with your cool Excel Tricks!
My pleasure Jobert!
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.
Thanks LG, and Bob, it was really helpful
Extremely helpful Thank You
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
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 Leila for sharing and for all the great videos you post. Always enjoy your insight. Stay safe excel goddess. 👩💻
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!
Exactly what I needed !!!!!
Thanks leila this is very helpful. Makes my life easier.
I am currently forced to use Excel 2019, and this video came in so handy, like many of your videos. Thank you so much!
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!
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?
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
Great tips thank you
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.
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
This is good stuff. Thanks very much Leila.
I eagerly waiting for your Excel videos... in every single video I learn new things... thanks 🥰
I'm happy to hear that Ashwin!
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?
Hi Leila every time I watch different Video and find something more interesting in Excel thank you.
Happy to hear that, Mohammad!
awsome video!
Thank you very much, from Germany
Leila, is there a way to put these values in a drop down list instead of just showing them in a table? Thanks!
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!
This is awesome, thank you so much!
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?
Solution vraiment géniale et très utile, merci encore pour tes effores Leila...❤
Glad you liked our formula marathon :)
That was amazing. Thank you.
Very well explained
Great Video, Leila. This is almost the problem I am having but not exactly. I have numbers in column A and B. I want to find the numbers in column B which are associated to the largest number in column A. this is easy. But my problem is when there are multiple occurances of max numbers in column A I want to find the largest associated number in column B. Any suggestions?
Great explanation. Thank you.
Thanks Leila, filter function is amazing 👍🏼
It sure is Stephen!
thank you so much ,for giving a valuable information
Thanks for you and Bob, you are doing much to the world.
Our pleasure :)
Omg you saved my life. Thank you so much
Incredible!! Thanks a lot!!!
Glad you liked it!
Very interesting and useful video. Thank you...
Great to learn from you. it is very rare results required.
Great Video!!! Thank you!
Thanks a lot Leila and Bob for the solution 😊
Our pleasure :)
Thanks you
great work
Hi Leila, do you have any video on a simplified Monte Carlo Simulation model ?
Thanks and respect TEACHER , Your explanation is great ,Excel easier than before
Glad you think so!
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.
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?
You are the best of the best
All time I found solution with you
Many thanks
Thanks Leila. Bob is the Master!
He sure is.
like the logic way you are teaching to us , easy to understand and put into practice
Glad to hear that.
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
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.
You are a God Gifted. 😍
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.
II like simplicity these simple formula's are easy to know, thank you
Glad you like them!
Hi Leila!Some Really Interesting Formulas(Thank You Bob),Feel Lucky I Got 365 And Can Use The Filter Function But Always Good To Learn Other Methods..Thank You :-)
Yes, FILTER is the best :)
thanks a lot, i really appreciate your effort
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....
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
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,
OMG Leila, You are the most beautiful equation.
Excellent Leila you rule! , God bless you more
hey, do you know a way to capture a signature for excel. I have a excel pilot logbook and I'd be better if I could capture signatures. also I do have wacam graphich tabled, so all I'd need is a good way to implement the signature to the cell
hello, I am from India..your lessons are helpful , thanks .
Thanks for the share.
So beautifully explained by the queen of Excel 😊
Thank you Peter! 🙂
Amazing solution Leila very easy to apply in routine work.
Glad you think so Jimmy!
is there a way we can use aggregate function within filter function? by filtering unique values of name and getting the sum of the sales value, adjacent to the names?
Thanks alot for this awesomeness, one step furthuer, can you sugguest how add another condition same as this in a different columbs
Thanks a Lot Leila
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
Genius approach ! Leila Fairy
Thanks for this great video Leila. How how about if the lookup criteria is continues column? Hopping for your expertise advice Ms. Leila.
Hey did you get any solution?
Thank you soo much 👍👍
Thanks a lot Leila this one really helpful in lot of reports...🥰😇👍👏
Glad to help.
Thanks Leila AND Bob!
Our pleasure :)
Great video! Why did we used conditional formatting in the helper column instead of the iferror function?
I think she was just demonstrating that either method would work for hiding the errors.