Check out the Complete Guide with 7 different approaches including an easy to digest (and remember) 2-step version: ruclips.net/video/5cBUIa31AiA/видео.html Grab the file I used in the video from here 👉 pages.xelplus.com/multiple-matches-file
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.
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!
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.
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
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...
Leila ! I am a young entrepreneur, and using your Excel course made me switch from an expensive program (375USD / month) to NO-COST solutions on excel. You can't imagine how much I want to thank you for literally saving my company during this Covid Hard time. MERCI beaucoup !!! Brieuc, from Thailand
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!
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.
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.
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.
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.
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 :)
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?
Hi. Leila Ghaharani. Before asking, I say I love you, I'm really lucky to find your channel. and help the crowd. I thank you very much. wish you a long life.
Thank you Leila. I used several tricks I learned on your course "Top Excel tips and formulas" in order to prepare an "automatic module for orders" for my job (I had to translate formulas in Italian too). I used expecially vlookup but this system has been the cherry on the cake of my module. My boss appreciated it.
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 RUclips 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))),"")
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
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!
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.
Leila. You are amazing teacher. I love the way you explain things. Your RUclipss are my go to instead of any other place. I really thank you for all your youtube help. You are the best.
Hey Leila, there is a new trick to handling multiple returned matches and it's super simple. The solution we've been waiting for: ruclips.net/video/eqB2mklvWSg/видео.html
Thank you! Thank you! Thank you! For hours I had been looking for a formula just like the TEXTJOIN portion of your video. Your explanation of both methods was very easy for me to follow. Can't say thank you enough for this video!! Keep up the wonderful work!
Hello Leila, I have been trying for sometime now to extract Dr account table and Cr Account table from my chart of accounts table. This approach has worked really well for me. Thank you very much Dear, really appreciate it.
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 :)
Very useful formula! It will take my team of financial analysts to the next level. Each analyst currently uses about 30 separate excel files to analyze accounting trends on small businesses, and they will now be able to review in 1 file. Big time-saver to be able to select their name from a dropdown, and have all their accounts in a list, that is then fed into database retrieve sheets.
Wow - thanks for the tips. I never really knew that IFERROR slowed down large data formulas - I'll be switching over to the IF now. Oh and this video just made my job much easier - Thanks again!
Hello Leila! The way you present really suits me. Clear and easy to follow. You know what, I would also like to be a geek like you. It's just... I'm 34 and feel it's too late...
Check out the Complete Guide with 7 different approaches including an easy to digest (and remember) 2-step version: ruclips.net/video/5cBUIa31AiA/видео.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.
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!
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
Had to watch it twice to wrap my head around it. Excellent as usual.
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.
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...
Amazing!! Thank you so much - nobody at my work could figure this out so now I look like a genius! :)
Happy to help!
You do such an excellent job giving a higher-level explanation while reviewing basic concepts. I really enjoy it.
Leila ! I am a young entrepreneur, and using your Excel course made me switch from an expensive program (375USD / month) to NO-COST solutions on excel. You can't imagine how much I want to thank you for literally saving my company during this Covid Hard time. MERCI beaucoup !!!
Brieuc, from Thailand
That's great to hear! All the best for your business!
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 your explanation. This helps me a lot! Been searching for this past two days and I was stuck before I came here.
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
I've been looking through old and unhelpful videos to find exactly this solution, This video was extremally helpful and well explained.
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.
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.
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
I had to watch this twice but you explained it very well. I am the Excel expert at every place I have worked but I am impressed by this. Good job.
I'm glad you like it.
Amazing tutorial, really very well explained. A huge thank you! was exactly what I was looking for.
I'm really glad you like it!
This tutorial has helped me quite a lot with filtering data in Excel. 10/10 for showing step-by-step with explanations for the first method.
Great to hear!
Great video on this very popular topic : )
Thank you Mike :)
One genius is replying to another... Great!
Thank you so much for all your support.
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.
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 :)
Tutorial bem explicado, o método dois é menos complicado, bons exemplos. Obrigado Leila.
You are an excellent teacher.
I prefer the first method.
You're amazing teaching us. Congratulations!!! 😍
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.
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?
Hi. Leila Ghaharani.
Before asking, I say I love you, I'm really lucky to find your channel. and help the crowd.
I thank you very much. wish you a long life.
Thanks for the kind words. It's great to have you here.
Ma'am i believe that array formula will slow down the calculation if the data is huge so thumps up for first formula👍
Thank you Leila. I used several tricks I learned on your course "Top Excel tips and formulas" in order to prepare an "automatic module for orders" for my job (I had to translate formulas in Italian too). I used expecially vlookup but this system has been the cherry on the cake of my module. My boss appreciated it.
I'm very happy to hear that Roberto. Glad the course was helpful for your work.
A quarter of the way into it I was like "Oh I see" half way mark "Nope. I quit."
If there is such a thing as an Excel celebrity, you are definitely one of them! Thank you so much, worked perfectly.
I'm honored :) Glad it's useful.
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 RUclips 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))),"")
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
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
Your videos are simply amazing
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.
Dear Leila.... amazing content with very simple instructions
Hey Leila, great video! are we able to concatenate the results into one cell? preferably without use CSE??
Leila. You are amazing teacher. I love the way you explain things. Your RUclipss are my go to instead of any other place. I really thank you for all your youtube help. You are the best.
Thank you! 😃
replacing the iferror with if function is really helpful, thanks a lot for the sharing.
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 for the second solution. You saved my life! I had a long worksheet to do this exact summery. Thank you again!
Hey Leila, there is a new trick to handling multiple returned matches and it's super simple. The solution we've been waiting for: ruclips.net/video/eqB2mklvWSg/видео.html
Great video. FILTER is the best! I covered it here ruclips.net/video/RBZHf9XPQ6w/видео.html and will be releasing more videos about it soon.
This actually works well for repeated values
Thank you! Thank you! Thank you!
For hours I had been looking for a formula just like the TEXTJOIN portion of your video. Your explanation of both methods was very easy for me to follow.
Can't say thank you enough for this video!! Keep up the wonderful work!
Great to hear!
Simply Amazing .... great fan of your teaching
So nice of you
best explanation of such complicated function... hats off to you...
Thank you!
saw ur video like.... hundread times and everytime i got amused... congratz really!
This was so helpful! Thank you so much!
This is a game changer! I love your work. Thanks!
You're very welcome!
Hello Leila, I have been trying for sometime now to extract Dr account table and Cr Account table from my chart of accounts table. This approach has worked really well for me.
Thank you very much Dear, really appreciate it.
Wow.. Really helpful.. You are walking us through each steps very clearly, so that anyone grasp it quickly. Thanks a lot Leila.
Unbelievably useful. The second solution was exactly what I needed i.e. inserting the results into a single cell.
Glad it helped Timmy!
It worked like a charm. Thanks Leila!
Great!
Great video! Thanks for posting!
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 :)
Its crazy that I finally understood this. Great tutorial !!
Glad to hear that!
@@LeilaGharani I've copied and paste some similar formula in the past, but this really the first time I understood whats going on. Many many thanks
Very useful formula! It will take my team of financial analysts to the next level. Each analyst currently uses about 30 separate excel files to analyze accounting trends on small businesses, and they will now be able to review in 1 file. Big time-saver to be able to select their name from a dropdown, and have all their accounts in a list, that is then fed into database retrieve sheets.
Excellent!
Thanks so much for your video
Very clever way to fuel the index function, I mean the row range stepping - nice!!!
This video inspires my creativity and I manage to produce great solution to my work. Appreciated 👍🏻
Wow - thanks for the tips. I never really knew that IFERROR slowed down large data formulas - I'll be switching over to the IF now. Oh and this video just made my job much easier - Thanks again!
Yes, it was very useful point.
First formula easy to keep up with if you have at least intermediate knowledge of excel. Excellent tutorial
Very nicely explained and in detail.. It worked for me WONDERFULLY !!! Thanks a lot Leila...
great job! easy to understand. this video solved my problem with my tracker.
Glad it helped, Thomas!
I was searching for this for a while now, thank you for the tutorial.
Great video.
Will be practicing this.
Iam awe struck by ur excel knowledge hats off you😱😱😱
Excellent tutorial, been searching for this for long. thank you for your work
Wow! Your Excel skills are really impressive.
I can't believe I actually managed to follow that and make it work. Huge help. Thanks
Glad it helped!
Looking for a video to understand "INDEX & AGGREGATE" & got this, just great. Keep it up.
Hello Leila! The way you present really suits me. Clear and easy to follow. You know what, I would also like to be a geek like you. It's just... I'm 34 and feel it's too late...
It's never too late!
SUPER tutorial. Definitely a channel to subscribe to. Thanks.
Awesome, thank you Stefan!
Your Excel formulas are almost as beautiful as you. Thank you!
you have brilliant skill of teaching
You are amazingly wonderful. I have been watching your videos for a while now and they all have been very helpful.
Absolutely delighted with this TEXTJOIN formula thanks so much !
Great to hear!
Thank you for this tutorial, I have been looking for a while how to do this.
Amazing, very helpful!
Very nicely explained madam. Thank you
Awesome! I will definitely make use of this!
thanks, your post saved my day
Nice video thanks for posting
I am sure if I see your videos regularly so In a long run I definitely become an omneceant teacher.
Thanks for your precious videos.
Keep going on
You're very welcome Faisal. I hope the videos will be helpful.
@@LeilaGharani ya it is.
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 🙏🙏.
that was an intense mind bending 10 minutes
😊
Hey thanks, you saved me a lot! Great explanation.
This worked amazing! Used it to return FEMA disasters since one zip code can have multiple disasters
Explained clearly - Really admire your skill in excel and simple demonstration.
Thank you very much for this valuable video
Excellent explanation.
Thank you, thank you, thank you!! This is exactly what solved the problem I have been searching for all day - almost gave up. Much appreciated!!!
My pleasure. I'm happy it's helpful for you.
Thousands thanks Leila!!! This helped me a lot!! Especially the textjoin. For a dynamic Calendar. (Which Formatting still needs improvement😁)
Glad it was helpful!
I've literally been looking for this formula, written just this way for years. Thank You!!
U shouldn't ask if video was useful coz ur videos r always useful for excel users... Awesome video as always!!
Many thanks for the kind words!
Leila you ROCK! It was the solution to what I was looking for and it was easy to understand!
I'm very happy to hear that!
Excellent Explanation...
I keep coming back to yours tutorials thank you for sharing.
Happy to hear that!