Return Multiple Match Results in Excel (2 methods)
ฝัง
- เผยแพร่เมื่อ 15 พ.ค. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Quickly learn how to lookup one value in Excel and return multiple matches.
VLOOKUP and INDEX & MATCH formulas always return the first match they find inside a data table. What if you wanted to return multiple matches? I'll show you two different methods:
⬇️ Download the workbook here: pages.xelplus.com/multiple-ma...
In this video, you'll discover:
▪️ Method 1: INDEX & AGGREGATE: Dive into a more complex approach that works on all Excel versions without requiring CSE (Control + Shift + Enter).
▪️ Method 2: TEXTJOIN Function: Explore a simpler, more straightforward method ideal for users with Excel 2016 and Office 365. This method utilizes CSE for optimal results.
▪️ Practical Examples: Learn through real-world scenarios, making it easier to apply these techniques to your own Excel projects.
▪️ Dynamic Data Sets: Understand how to work with various data sets, enhancing your flexibility and adaptability in Excel.
00:00 How to Find Multiple Match Results in Excel
01:10 How to Use INDEX & AGGREGATE to Find Multiple Matches in Excel
11:12 How to Use TEXTJOIN to Find Multiple Matches in Excel
13:36 Wrap Up
★ Links to related videos: ★
VLOOKUP basics: • Excel VLOOKUP: Basics ...
INDEX MATCH basics: • How to use Excel Index...
Get Unique List: • Extract UNIQUE Items f...
TEXTJOIN: • How to Solve Complex L...
➡️ Join this channel to get access to perks: / @leilagharani
🎓 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
Check out the Complete Guide with 7 different approaches including an easy to digest (and remember) 2-step version: th-cam.com/video/5cBUIa31AiA/w-d-xo.html
Grab the file I used in the video from here 👉 pages.xelplus.com/multiple-matches-file
Quick question, what if you have multiple criteria? I want to use an OR function, but excel won't let me.
why this video not downloading
how do you use this function where the data set is on a different sheet? I got the function to work but for some reason it will not return the last value in the data set. I think it has something to do with subtracting the first row. INDEX('Pool List'!$B:$B,AGGREGATE(15,3,('Pool List'!$A:$A=Agricola!$A$2)/('Pool List'!$A:$A=Agricola!$A$2)*ROW('Pool List'!$A:$A)-ROW('Pool List'!$A$1),ROWS('Pool List'!$A$2:A3)))
First of all thank you, I am learning from a smart brain.... 😇. I am facing a problem. The problem is if I have say in columns these values 30 40 50 60 70. Now if my value is below30 or 30 it should show 30.similarly, If I type below say 50 but greater than 40 it should show 50. The same for others.This I have solved. Now the problem starts. Say if in column with value 50 (as above) I have other values in the same column say 2, 3, 4, 5. Now I want that in that selected column with value 50, if my value is between 2 and 3 it should select the higher value 3. Or if my value is between 4 and 5 it should select 5.
Now this process of selecting should be same for other column values such as 40 60 or 70.
I hope you understand what I have to say, otherwise I can post xl sheet to you. Thank you.
Hello my friend,assume you are from India beauty from the bottom of my heart@@@@@Guar lick kee?Kee sai ho?Aip ma num kee hei?Mei lai num keith.Mei ah bee Hindi,Hindu she lai dar hung Indian,Pakerstain dos bananna jar dar hung.Mei jar lai ha hung,Bo ho tek kee hung.Mei soon lai jar dar hung.So kar lee ah.Bar mei link it,mei ban lee.
You're amazing teaching us. Congratulations!!! 😍
The second formula was easier to digest 🤣, while the first one was intimidating..I am a regular follower of your tutorials...this one seemed to be the most challenging...keep updating...
Absolutely phenomenal stuff! I was previously aware of using helper columns or concatenations within lookup formulas to derive new unique fields for lookup purposes, but in this current project I'm doing, I needed to extract values connected to duplicate values without using helper/concatenation features.
I've come across a small handful a Excel gurus on TH-cam that I've found to be great at explaining complex concepts & formulas in simple terms, but you take the cake. You're the elite of the elite and the only one I'm currently subscribed to for Excel-related videos. You've helped me with numerous projects. I'll be sending all my colleagues your way, thank you!
Amazing tutorial, really very well explained. A huge thank you! was exactly what I was looking for.
I'm really glad you like it!
Had to watch it twice to wrap my head around it. Excellent as usual.
You do such an excellent job giving a higher-level explanation while reviewing basic concepts. I really enjoy it.
Tutorial bem explicado, o método dois é menos complicado, bons exemplos. Obrigado Leila.
Very useful and informative video. Thank you. Power Query option also works great. After importing the range into Power Query, we can Group By using "Division". Thereafter adding a custom column using Table.Column function which will create a list of "Apps" column values. Then we can extract those values either through "Expand to new rows" or "Extract values" using a custom delimiter in the same row.
If I'd still be in school and you were my teacher, you'd be my favourite one! I could just listen to you all day. And thanks for the lesson, it's excellent! Like!
Glad you like it!
Hello Leila. Great video. Excellent explained. John Walkenbach and mr Excel would be proud on your “mega formula”! I prefer the second solution but can you return the result in multiple cells without power query? For less advanced users I would however prefer another solution: add a help column with: =IF(cell=cell-tobe-found, row() , “ “). Then continue with your solution to use SMALL to collect all row numbers.
Oh my gosh! Thank you so much for this! I was searching for a way to do this all over (for the first version, to have a list of results based on one criteria) and I finally found a video that explains perfectly how to achieve what I needed. Thank you!
Explained clearly - Really admire your skill in excel and simple demonstration.
Thank you very much for your great videos, Leila. I've learned a lot since the time I found your channel.
I've tried some formulas to extract unique values in order to make a dynamic drop-down list. They all work but the main problem that I haven't solved yet is the slow calculation of these formulas after the main dataset expands. This AGGREGATE formula in this video performs fast and great, but is there a way to change it in order to produce unique values only?
Your presentation is the most complete and specific I've ever seen. The value of this is critical to our analysis for a software implementation. Keep up this good work, you're a star!
Wow, thank you!
Thank you SO MUCH for this video - it took me a few passes through it to begin to understand it, but it totally made me shine at work!! I stuck with the first formula and your tip about IFERROR v. IF was fantastic!
Wow.. Really helpful.. You are walking us through each steps very clearly, so that anyone grasp it quickly. Thanks a lot Leila.
I started my first real job back in October of 2019 and I did not have a very comprehensive understanding of excel. As I took on more and more assignments and as my datasets kept growing, I realized I needed more than just long nested IF functions that got exponentially harder to maintain. Your videos have helped me branch out and use different formulas that I never would have known existed, as well as become faster at determining what formula suits my needs for a particular problem. Thanks a lot for these videos! Keep doing what you're doing and stay safe in this difficult time period!
I'm glad the tutorials are helpful, Matt!
I must admit,this is one of the best tutorial I have seen. I am sure I wouldn’t have done this without your tutorial. Day 1 - tried method 2, day 2 - went for method 1. Excellent tutorial.
Pruoqe
Yp
Great job Leila!!! Very nice, subtle and finesse approach.
You never cease to amaze. Great job again. Thanks.
A masterclass. You have a great teaching style. Clear, concise, personable and your cadence is just right. I've watched many Excel tutorials and you're up there with the best
I'm very happy to hear that. Many thanks for the nice feedback Paul.
Great video on this very popular topic : )
Thank you Mike :)
One genius is replying to another... Great!
I've literally been looking for this formula, written just this way for years. Thank You!!
Thank you very much, Leila! I'm an instant fan!
Hey Leila, great video! are we able to concatenate the results into one cell? preferably without use CSE??
I need paracetamol after watching that lmao ! that was tough to keep up with !
I need one that's stronger than paracetamol
have some sleep , its even better 😆
Agree
Lol
Hahahaha
Thank you so much for all your support.
A+!!! Great fix. I was using a text formula that required a match helper column to function.
Great Video! One thing about the textjoin option. You can use a character code for a delimiter. Like CHAR(10) then apply text wrapping and it will stack the results in one cell.
Underrated comment
Ma'am i believe that array formula will slow down the calculation if the data is huge so thumps up for first formula👍
Complex but very powerful! Keep up the good work!
This was so helpful! Thank you so much!
Hello! Is there a way to separate each matched value in Part 2 so that the apps returned are in separate cells in the same column? Can this column then be reference for a drop down box? Thanks!
i use left/right formulas with a find function to find all the cells to the left/right of the comma and break it out. little painful but i had a maximum of ~3-5 returning data fields using the second method. i.e =RIGHT(X2,LEN(X2)-FIND(",",X2)) on a cell where you input the formula from method 2 and used a comma as your delimiter, this will give you everything to the right of your first comma, rinse and repeat.
Thanks Danni for that solution, it works well. Alessandro, I also solved this with "text to colums" in "data", but one has to copy and paste as values to get a kind of CSV in a cell. It is not automatic, as you may need to transpose the values again to have a column for the drop down box.
you have brilliant skill of teaching
You helped me a lot to get good at excel, thank you so much! wish all sucess for you!
What if you have multiple criteria, .e.g. "Game" AND "Revenue"? Is there a way to add aggregate functions? Thank you! Great content!
Same question, did you ever find an answer?
figured it out thought id share the solution for anyone who comes this video and wants an answer to this question. Just concatenate your lookups and criteria's with an "&". It is important that you do not put the individual concatenations in their own parenthesis because it will break. There is no need to use CSE because the aggregate function is already handling the array. heres an example of what I did to return the second iteration of this lookup when several conditions are met. Thanks @Leila Gharani your TH-cam and XelPlus courses are required for my employees.
=IF([@Order]=3,ROW(INDEX(StitchBond[RIGHT],AGGREGATE(15,3,((StitchBond[RIGHT]&StitchBond[DT DESCRIPTION]=[@[RFGRC'#]]&$P$6)/(StitchBond[RIGHT]&StitchBond[DT DESCRIPTION]=[@[RFGRC'#]]&$P$6)*(ROW(StitchBond[RIGHT])-1)),2))),"")
Leila if i want to set both “Game” and “Productivity” as a criteria, what can i do for it?
Hi Furkan.. saw your question. If you have new dynamic array aware EXCEL, this would extract all the records with Game and Productivity and also sort according to Division: =SORT(FILTER(A5:C14,(A5:A14="Game")+(A5:A14="Productivity")),1). If you don't have new EXCEL, it can still be done, but is more complicated. I hope this helps. Good luck!
@@wayneedmondson1065 thanks for response
You are an excellent teacher.
I prefer the first method.
This is one of those things where I didn't realize I needed it until I saw your video title... Great video, even 3 years later it still works! Thanks :)
A quarter of the way into it I was like "Oh I see" half way mark "Nope. I quit."
Hey Leila, there is a new trick to handling multiple returned matches and it's super simple. The solution we've been waiting for: th-cam.com/video/eqB2mklvWSg/w-d-xo.html
Great video. FILTER is the best! I covered it here th-cam.com/video/RBZHf9XPQ6w/w-d-xo.html and will be releasing more videos about it soon.
This actually works well for repeated values
Great tutorial, very clear and well explained. Thanks for sharing your knowledge!
Thank you!! You have no idea how much this helped me!
Why don't we just do a pivot like: Rows: Division, Apps. I did it and looks just the same and only takes one minute
Thank you so much for your explanation. This helps me a lot! Been searching for this past two days and I was stuck before I came here.
Amazing. Found the perfect formula what I was looking for. Thank you very much. Very Useful.
This is very helpful!!!!! Thanks a lot Leila!
MASTERPIECE! THANK YOU SO MUCH!
Amazing stuff, used the Textjoin function to note how which ones are multiple matched. Thanks
Great example, thanks for the video!
Thank you so much! Just exactly what I was looking for.
You are the best Leila!
Fantastic step-by-step, this was so helpful in condensing a huge data sheet I had into a simple to view front page. Thanks so much.
This was helpful and I appreciate it, so thank you.
Excellent presentation. I learnt something new. however, what if I had duplicate data for a purpose for eg: at row 7 i had Game-Apps and at Row #10 I had Game-Apps again. How would I be able to get the same result except for the repetition of data.
Thanks a lot for this video!!! You helped me finding the solution I was seeking for, in a very clear and informative way!
Your videos are simply amazing
Amazing tips!!!! Thank you so much :)
this is just what i needed, thank you so much
Hey thanks, you saved me a lot! Great explanation.
replacing the iferror with if function is really helpful, thanks a lot for the sharing.
Thank you for sharing this.
We can make it also with array row method. But sometimes i use this method too.
I've been looking for this type of solution for a month, and finally, I found this video. Well explained and easy to follow. Thank you!
I'm glad to hear the tutorial is useful Michael.
I've been looking through old and unhelpful videos to find exactly this solution, This video was extremally helpful and well explained.
Thank you A MILLION TIMES. I used the textjoin function with googlesheets because I don't have the current version of excel, and it works exactly as mentioned. Thanks again :)
Great video.
Will be practicing this.
Very useful video. You make Excel look so simple and helpful.. great work. Many thanks!
Your teaching process is exultant,Impress,awesome.
Just what I need! Thanks so much! You're awesome! I love all your tutorials. They meet what I need at work. Keep them coming! Definitely a great teacher!
You're very welcome. Glad you find the tutorials useful.
after watching it few times, I fell in love with you!!! you deserve an award for just this video. millions of thanks, I really needed it.
I'm glad to hear the video was helpful :)
Awesome. You make it simple. Thanks.
I've been using method 1 for years, never understood how it works, just that it does. Now it makes sense, but I'll go with method 2 to make my life easier. Thank you so much my Excel goddess.
Amazing, very helpful!
Thanks, It's Very Helpful
Thank you very much for this valuable video
I keep coming back to yours tutorials thank you for sharing.
Happy to hear that!
Awesome video and the way you explain things 🙏🙏
Excellent tutorial, been searching for this for long. thank you for your work
Thanks! Worked like a charm!
Excellent as always! Thanks Leila!
Really worth of watching....I learned a lot from this channel.. thank you so much 😊
Great video! Thanks for posting!
Hey Leila ! as usual you rocks with this vedio, brilliant trick..Keep sharing
Awesome! I will definitely make use of this!
Simply the best! Thanks... well explained
Thank you Leila Jan, that was brilliant
Excellent tutorial and very helpful as always!
Thank you so much! it's very helpful
Honestly one of the greatest bits of formula I have ever seen. Hopefully one day I can write it without needing to watch this video, but until that day we have Leila! 11/10
Absolutely delighted with this TEXTJOIN formula thanks so much !
Great to hear!
Looking for a video to understand "INDEX & AGGREGATE" & got this, just great. Keep it up.
You are amazingly wonderful. I have been watching your videos for a while now and they all have been very helpful.
Thank you for the second solution. You saved my life! I had a long worksheet to do this exact summery. Thank you again!
Thank you for this tutorial, I have been looking for a while how to do this.
Hello! Thank you so much for this video. It has been really helpfull as many of your videos.
thanks, your post saved my day
Thanks so much for sharing this info!
One of the best explanation mam for this problem, I saw multiple blogs and videos but mostly were not able to explain.. Thank you so much mam 🙏🙏.
Great video. Thank you! You're a great teacher and absolutely one of my favorites.
Thank you very much Jan. Really glad to hear that.
I was searching for this for a while now, thank you for the tutorial.