Check out the Complete Guide with 7 different approaches including an easy to digest (and remember) 2-step version: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-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.
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!
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!
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...
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 :)
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!
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.
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!
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?
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!
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.
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.
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!!
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.
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 RU-vid 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))),"")
Hey Leila, there is a new trick to handling multiple returned matches and it's super simple. The solution we've been waiting for: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-eqB2mklvWSg.html
Great video. FILTER is the best! I covered it here ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-RBZHf9XPQ6w.html and will be releasing more videos about it soon.
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
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.
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.
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!
Okay, got this one too...just have to practice, so I can formulate it faster, on demand, without reference... Thank you, very useful material. I already got a shorter version. Thanks for pointing me in the right direction: =IFERROR(INDEX(B:B,AGGREGATE(15,3,(A:A=$G$4)/(A:A=$G$4)*(ROW(A:A)),ROWS($F$5:F5))),"")
Respected leila madam, Many lectures, examples, tutorials are available for ms Excel. But very few are available for ms access. I suggest that you to focus on ms access. Thanking you.
I modified your method/formula for my needs and got it to work with my 3 criteria. But now I want to have a wildcard option that will give me all the records that match the first 2 criteria.
Such a great solution to this issue! It works great in my file. Some of the results come up as a zero, because of cell they are referencing are blank. Is there a way of getting rid of this and keep the result blank? Thanks so much, Leila
It never fails to amaze me how creative people are with Excel formulas. However, I can't imagine having to maintain a large spreadsheet with these complex formulas. Version control, audit trail, debugging will be a complete nightmare.
