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.
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!
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.
Seven differ approaches for finding multiple matches explained in less than 13 minutes. Truly amazing. Can't wait till the day all of us can share in your excitement for the utopia that is the Filter function.
In my scenario we need multiple results matched but based on two criteria not the one - Its a big pain but currently all working. These methods look good I may see if I can mod the formulas being used, that filter function looks like a dream! - I'll look into it. Thank you
Very nice video, OFFSET is very versatile, it can do miracles, like indirect or other functions, unfortunately is volatile, so as much as is very tempting to use it, for large data sets, alternatives should always be considered.
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)
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,
Thanks Leila for very interesting video :-) I would like to add one remark - no offence: If the user do not understand what OFFSET is doing behind the scene then sometimes using OFFSET (or other volatile functions) is a best way to kill the workbook (in meaning, slow work of excel) This is the reason that i don't use it often. Mostly, we can replace OFFSET by INDEX : INDEX
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!
I'm a little late to the party but still never missed a formula video from the Queen. Very informative as always, thank you. Please lets us know when you will publish your new Udemy class for power query. Thank you
Created a sheet exactly like hers. Added the match statement in column F row 4. =MATCH($G$4,OFFSET($A$1,F4,,1000),0) Her results are 7 then 1 then ___ my results are 7 then 2 then 5 then 2 then 5 then 2 When I add the plus F4 I get 7 then 9 then 11. Row 8 is not being counted. To trouble shoot it even though I already double checked the spelling and deleted extra spaces, I changed all of the Game entries to 1Game except for row 8. Retyped and changed formula until the row 8 was picked up. Changed 1Game back to Game and re ran and it works exactly as her example.
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!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 :-)
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?
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.
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, really appreciate good work for help us to understand and learn best use of excel for data analysis in easy and simple way. I have just one simple suggestion, yes your videos are little lengthy as most of them are over ten minutes, for better impact and views, these should ideally be around 5-7 minutes. Thanks
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 =]
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
Dear Leila, your channel is great, i have learned a lot from it. I need your help if i want i want to find multiple match results between two dates, how would it accommodate in this formula of Bob or yours. let me give you my problem, i want to extract all values of one account between two dates, for example records of sales account between 01/07/2020 to 31/07/2020. I have tried many things but no result , please help me, both in bob's formula and your old formula of index/aggregate. I shall remain thankful.
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
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.
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 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
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?
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 I really appreciate the effort you make and the filter function is something made excel a more fun application to use. I wonder if using filter for multiple criteria can assume one of the criteria is blank
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?
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.
This Girl is Amazing, your channel helps me to be pro in Excel.
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 :)
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!
I am currently forced to use Excel 2019, and this video came in so handy, like many of your videos. Thank you so much!
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 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.
You are the best Leila, you went beyond and provided alternatives to people.
I always refer your videos while I prepare Dashboards.
Thanks for your videos.
Thanks a Lot Leila, Indeed you're great. Stay Blessed
Thank you Robert for dropping by.
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!
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....
like the logic way you are teaching to us , easy to understand and put into practice
Glad to hear that.
Thanks and respect TEACHER , Your explanation is great ,Excel easier than before
Glad you think so!
Seven differ approaches for finding multiple matches explained in less than 13 minutes. Truly amazing.
Can't wait till the day all of us can share in your excitement for the utopia that is the Filter function.
Always good to have options. Hopefully soon utopia will be around the corner for you :)
Thank you for showing so many different method to accomplish the goal.
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.
Hi Leila every time I watch different Video and find something more interesting in Excel thank you.
Happy to hear that, Mohammad!
Thank you for enlightening me Leila with your cool Excel Tricks!
My pleasure Jobert!
Thank you this works like magic!
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
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
hello, I am from India..your lessons are helpful , thanks .
Brilliant! Thank you Leila for sharing all those variations with us. I learn a lot from you.
In my scenario we need multiple results matched but based on two criteria not the one - Its a big pain but currently all working. These methods look good I may see if I can mod the formulas being used, that filter function looks like a dream! - I'll look into it. Thank you
Hey how can we do it based on two criterion. Do let me know
How do you do it with multiple criteria?
Great to learn from you. it is very rare results required.
I love the new 365! Have been a huge help already for me in my work
You are the best of the best
All time I found solution with you
Many thanks
Thanks for you and Bob, you are doing much to the world.
Our pleasure :)
Thanks LG, and Bob, it was really helpful
I eagerly waiting for your Excel videos... in every single video I learn new things... thanks 🥰
I'm happy to hear that Ashwin!
Very nice video, OFFSET is very versatile, it can do miracles, like indirect or other functions, unfortunately is volatile, so as much as is very tempting to use it, for large data sets, alternatives should always be considered.
True. It's always important to consider the context.
So beautifully explained by the queen of Excel 😊
Thank you Peter! 🙂
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)
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,
These are great methods to solve a common problem I have. Thank you! Your explanations are always very clear!
Happy to help, Simon!
awsome video!
Thank you very much, from Germany
Thanks Leila for very interesting video :-)
I would like to add one remark - no offence: If the user do not understand what OFFSET is doing behind the scene then sometimes using OFFSET (or other volatile functions) is a best way to kill the workbook (in meaning, slow work of excel) This is the reason that i don't use it often.
Mostly, we can replace OFFSET by INDEX : INDEX
Good point! Thanks for dropping by Bill.
Thanks leila this is very helpful. Makes my life easier.
Instead of using offset you can give column reference like A:A
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!
thanks, this is what I am looking for multiple matching rows.
I'm a little late to the party but still never missed a formula video from the Queen. Very informative as always, thank you. Please lets us know when you will publish your new Udemy class for power query. Thank you
Glad you liked the formula marathon :) For Power Query we're still on track for end of June.
Created a sheet exactly like hers. Added the match statement in column F row 4. =MATCH($G$4,OFFSET($A$1,F4,,1000),0) Her results are 7 then 1 then ___ my results are 7 then 2 then 5 then 2 then 5 then 2 When I add the plus F4 I get 7 then 9 then 11. Row 8 is not being counted. To trouble shoot it even though I already double checked the spelling and deleted extra spaces, I changed all of the Game entries to 1Game except for row 8. Retyped and changed formula until the row 8 was picked up. Changed 1Game back to Game and re ran and it works exactly as her example.
Thanks Leila. Bob is the Master!
He sure is.
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!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 Leila and Bob for the solution 😊
Our pleasure :)
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
Amazing solution Leila very easy to apply in routine work.
Glad you think so Jimmy!
Thanks mam for such amazing excel videos and also thank Mr Bob
Most welcome!
Extremely helpful Thank You
thank you so much ,for giving a valuable information
II like simplicity these simple formula's are easy to know, thank you
Glad you like them!
A great work Leila. Thank you very much for your explanation!
Excellent Leila you rule! , God bless you more
You are a God Gifted. 😍
Very interesting and useful video. Thank you...
Thanks alot for this awesomeness, one step furthuer, can you sugguest how add another condition same as this in a different columbs
Very well explained
Omg you saved my life. Thank you so much
Solution vraiment géniale et très utile, merci encore pour tes effores Leila...❤
Glad you liked our formula marathon :)
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.
This is good stuff. Thanks very much Leila.
Try in getting the Apps and Revenue instead of index use Indirect(“B”&F5) for Apps and Indirect(“C”&F5) for Revenue results
Thanks Leila, filter function is amazing 👍🏼
It sure is Stephen!
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?
Your page is booming baby girl ...You have close to 2 million subscribers ....I know them youtube checks 🔥🔥🔥🔥
Leila, you are doing amazing, really great.
Leila, Thanks for this nice approach, really good.
Exactly what I needed !!!!!
Well Done Leila
Hi Leila, really appreciate good work for help us to understand and learn best use of excel for data analysis in easy and simple way. I have just one simple suggestion, yes your videos are little lengthy as most of them are over ten minutes, for better impact and views, these should ideally be around 5-7 minutes. Thanks
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 =]
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
Great Video!!! Thank you!
Thanks Leila for sharing and for all the great videos you post. Always enjoy your insight. Stay safe excel goddess. 👩💻
Dear Leila, your channel is great, i have learned a lot from it. I need your help if i want i want to find multiple match results between two dates, how would it accommodate in this formula of Bob or yours. let me give you my problem, i want to extract all values of one account between two dates, for example records of sales account between 01/07/2020 to 31/07/2020. I have tried many things but no result , please help me, both in bob's formula and your old formula of index/aggregate. I shall remain thankful.
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
Great explanation. Thank you.
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.
Incredible!! Thanks a lot!!!
Glad you liked it!
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?
Great learning Leila! What if i will search or filter more than one item.lets say 5 item. How can i make it works.
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.
Thanks a lot Leila this one really helpful in lot of reports...🥰😇👍👏
Glad to help.
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
We know F4 for cell reference, but it can also be used to repeat the same action we have just applied
Wow. But I'm too stupid to follow that method. Thank God there's FILTER function. You are great.
Filter is amazing!
Great tips thank you
Thanks Leila AND Bob!
Our pleasure :)
OMG Leila, You are the most beautiful equation.
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?
I think INDIRECT also applies ... I actually use OFFSET(Match.... and that works better than Index, since everything is OFFSET from the MATCHed value.
That was amazing. Thank you.
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
I really appreciate the effort you make and the filter function is something made excel a more fun application to use. I wonder if using filter for multiple criteria can assume one of the criteria is blank
If it can assume the blank criteria is true?
Thanks a Lot Leila
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?