Check out the Complete Guide with 7 different approaches including an easy to digest (and remember) 2-step version: ua-cam.com/video/5cBUIa31AiA/v-deo.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.
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!
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
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.
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.
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.
So many thanks I have had a program I wrote sometime ago and have been adding several rows/columns by hand, I could not find a method of auto row/column info from my sheets to my main sheet, and I tried a few!...UNTIL NOW, this has made adding info from several sheets (that I did by hand) to my main page a matter of a SINGLE entry on that page...THANK YOU THANK YOU THANK YOU.
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!
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...
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.
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
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, 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.
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 :)
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.
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
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 :)
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.
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.
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!
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 UA-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))),"")
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!
Tks for your step by step explanation that makes it easy to catch up with. I suppose there is another way to do multiple lookup using function "Filter" that you've shown in your other video.
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.
Hey Leila, there is a new trick to handling multiple returned matches and it's super simple. The solution we've been waiting for: ua-cam.com/video/eqB2mklvWSg/v-deo.html
Well done Leila! A couple of nice hacks such as dividing the array by itself to error the falses also using the row formula to build a counter. Hacks! Erroring the false value could be done with *1/1 to make it shorter.
Leila. You are amazing teacher. I love the way you explain things. Your UA-cams are my go to instead of any other place. I really thank you for all your youtube help. You are the best.
Hey Leilah, Many thanks for the great video! One more way to go with array formula instead of using the aggregate function is that: {=INDEX($B$5:$B$14,SMALL(IF($J$4=$A$5:$C$14,ROW($A$5:$C$14)),ROW(1:1))-Row($A$4))}
Hello Leila thanks for explaining a complex function like this in a very simple way I have become huge fan of Index and Match function now, but recently I was having problem in using the same as my data is very large and contains multiple duplicate data value please upload a solution video for same.
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 UA-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!
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...
Pure genious!! Wish I could generate complex formulas like this one from the scratch... Just amazing. Many thanks for helping me deal with my challenge!
I'm a relative novice and had to use your first option due to the version I'm working on but within an hour I had done it. I would recommend a trouble shoot section as mine wouldn't work unless I removed the "-row" to ignore the title row. Thanks for the great video!
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!
hmm...I'm a Google Sheets user and the filter function by far is so easy. I think Excel is using it too by now. But not knocking this as most insightful to see multiple functions being used. Thanks!!
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!
Thank you *so much* for this. This has been a problem for me so many times and I've never found a good solution. This one is a little fiddly but it's both robust and flexible. I'll be committing this one to memory!
Thanks a ton Leila for explaining such complex formula in such a simple and easy way. This one really helped me out. Also can you please make a video on how to use or any other alternate option for the "textjoin" formula if we dont have the latest version of excel. Once agin thank you very much for this video.
Indeed, alternatvie to Textjoin would be nice. In many places older versions of Excel are usually in use (2013 and sometimes 2016), so would be nice not to stick simply to latest version options in such videos.
This was highly informative, thank you, I've been wanting this on one of my sheets but had to settle with a manual filter option..... until now. I wouldn't have worked out the maths behind it without your step-by-step breakdown either.
Check out the Complete Guide with 7 different approaches including an easy to digest (and remember) 2-step version: ua-cam.com/video/5cBUIa31AiA/v-deo.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.
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!
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!
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!
Thank you so much for all your support.
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
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.
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.
So many thanks I have had a program I wrote sometime ago and have been adding several rows/columns by hand, I could not find a method of auto row/column info from my sheets to my main sheet, and I tried a few!...UNTIL NOW, this has made adding info from several sheets (that I did by hand) to my main page a matter of a SINGLE entry on that page...THANK YOU THANK YOU THANK YOU.
Glad it helped!
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!
I've been looking through old and unhelpful videos to find exactly this solution, This video was extremally helpful and well explained.
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...
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 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.
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!
You do such an excellent job giving a higher-level explanation while reviewing basic concepts. I really enjoy it.
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.
Had to watch it twice to wrap my head around it. Excellent as usual.
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.
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
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.
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
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 :)
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.
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
Tutorial bem explicado, o método dois é menos complicado, bons exemplos. Obrigado Leila.
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 :)
Amazing tutorial, really very well explained. A huge thank you! was exactly what I was looking for.
I'm really glad you like it!
Amazing!! Thank you so much - nobody at my work could figure this out so now I look like a genius! :)
Happy to help!
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👍
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!
You're amazing teaching us. Congratulations!!! 😍
replacing the iferror with if function is really helpful, thanks a lot for the sharing.
Great video on this very popular topic : )
Thank you Mike :)
One genius is replying to another... Great!
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!
Hey Leila, great video! are we able to concatenate the results into one cell? preferably without use CSE??
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 UA-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))),"")
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.
A quarter of the way into it I was like "Oh I see" half way mark "Nope. I quit."
Thank you for the second solution. You saved my life! I had a long worksheet to do this exact summery. Thank you again!
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
Wow.. Really helpful.. You are walking us through each steps very clearly, so that anyone grasp it quickly. Thanks a lot Leila.
Tks for your step by step explanation that makes it easy to catch up with. I suppose there is another way to do multiple lookup using function "Filter" that you've shown in your other video.
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.
Very clever way to fuel the index function, I mean the row range stepping - nice!!!
Hey Leila, there is a new trick to handling multiple returned matches and it's super simple. The solution we've been waiting for: ua-cam.com/video/eqB2mklvWSg/v-deo.html
Great video. FILTER is the best! I covered it here ua-cam.com/video/RBZHf9XPQ6w/v-deo.html and will be releasing more videos about it soon.
This actually works well for repeated values
Well done Leila! A couple of nice hacks such as dividing the array by itself to error the falses also using the row formula to build a counter. Hacks! Erroring the false value could be done with *1/1 to make it shorter.
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
Leila. You are amazing teacher. I love the way you explain things. Your UA-cams are my go to instead of any other place. I really thank you for all your youtube help. You are the best.
Thank you! 😃
Hey Leilah,
Many thanks for the great video!
One more way to go with array formula instead of using the aggregate function is that:
{=INDEX($B$5:$B$14,SMALL(IF($J$4=$A$5:$C$14,ROW($A$5:$C$14)),ROW(1:1))-Row($A$4))}
First formula easy to keep up with if you have at least intermediate knowledge of excel. Excellent tutorial
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.
Hello Leila thanks for explaining a complex function like this in a very simple way I have become huge fan of Index and Match function now, but recently I was having problem in using the same as my data is very large and contains multiple duplicate data value please upload a solution video for same.
This worked amazing! Used it to return FEMA disasters since one zip code can have multiple disasters
I've literally been looking for this formula, written just this way for years. Thank You!!
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 UA-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!
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 🙏🙏.
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.
You are an excellent teacher.
I prefer the first method.
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!
Unbelievably useful. The second solution was exactly what I needed i.e. inserting the results into a single cell.
Glad it helped Timmy!
Thousands thanks Leila!!! This helped me a lot!! Especially the textjoin. For a dynamic Calendar. (Which Formatting still needs improvement😁)
Glad it was helpful!
Looking for a video to understand "INDEX & AGGREGATE" & got this, just great. Keep it up.
This video inspires my creativity and I manage to produce great solution to my work. Appreciated 👍🏻
Dear Leila.... amazing content with very simple instructions
Pure genious!! Wish I could generate complex formulas like this one from the scratch... Just amazing. Many thanks for helping me deal with my challenge!
Happy to help!
Explained clearly - Really admire your skill in excel and simple demonstration.
Iam awe struck by ur excel knowledge hats off you😱😱😱
Amazing stuff, used the Textjoin function to note how which ones are multiple matched. Thanks
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.
I'm a relative novice and had to use your first option due to the version I'm working on but within an hour I had done it. I would recommend a trouble shoot section as mine wouldn't work unless I removed the "-row" to ignore the title row. Thanks for the great video!
saw ur video like.... hundread times and everytime i got amused... congratz really!
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!
Very nicely explained and in detail.. It worked for me WONDERFULLY !!! Thanks a lot Leila...
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.
Thank you, Leila. It’s one of the most useful videos on Excel I’ve ever seen!
Glad you think so!
Wow! Your Excel skills are really impressive.
Thank you for this tutorial, I have been looking for a while how to do this.
you are an angle sent by god.. this is amazing and you have deep knowledge of excel. by the way you are a good teacher too
bruh!!! a-cute angle!
A+!!! Great fix. I was using a text formula that required a match helper column to function.
Been wondering for a long time if this could be done in excel with everyday formulas. Excellent!!!
Glad you like the video!
Thanks for sharing the computation, i have worked on text join formula without CSE. in if formula have made G4=A5:A14. This way you don't need CSE
I can't believe I actually managed to follow that and make it work. Huge help. Thanks
Glad it helped!
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!
hmm...I'm a Google Sheets user and the filter function by far is so easy. I think Excel is using it too by now. But not knocking this as most insightful to see multiple functions being used. Thanks!!
Yes, the Filter function also arrived in Excel and it immediately became my new favorite :)
great job! easy to understand. this video solved my problem with my tracker.
Glad it helped, Thomas!
Watched video four times.... Every time I was learning new formulae application and choice where to use it...
First method was more logical for
brilliant. works better on text strings to convert dates to text
Absolutely delighted with this TEXTJOIN formula thanks so much !
Great to hear!
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!
best explanation of such complicated function... hats off to you...
Thank you!
literally saved my life, used this application for capstone project and last minute fix worked perfectly!!!
I'm glad to hear it was helpful!
Thank you *so much* for this. This has been a problem for me so many times and I've never found a good solution. This one is a little fiddly but it's both robust and flexible. I'll be committing this one to memory!
Great to hear!
Thanks a ton Leila for explaining such complex formula in such a simple and easy way. This one really helped me out. Also can you please make a video on how to use or any other alternate option for the "textjoin" formula if we dont have the latest version of excel.
Once agin thank you very much for this video.
Indeed, alternatvie to Textjoin would be nice. In many places older versions of Excel are usually in use (2013 and sometimes 2016), so would be nice not to stick simply to latest version options in such videos.
you have brilliant skill of teaching
Thanks a lot for this video!!! You helped me finding the solution I was seeking for, in a very clear and informative way!
This was highly informative, thank you, I've been wanting this on one of my sheets but had to settle with a manual filter option..... until now. I wouldn't have worked out the maths behind it without your step-by-step breakdown either.
You can use the filter formula now