I've watched so many videos on the subject and this was by far the easiest. Easy to understand, well explained and made my life so much easier. Thank you!
Nice video! Here's a legacy formula version using AGGREGATE that does not require CSE: =IFERROR(INDEX($B$2:$B$8,AGGREGATE(15,6,(1/($A$2:$A$8=$D$2)*(ROW($A$2:$A$8)-ROW($A$2)+1)),ROWS(K$2:K2))),""). And of course now we can get all this done easily with the new FILTER function, as in: =FILTER(B2:B8,A2:A8=D2) which spills the results dynamically and does not require IFERROR. Fun to solve these in different ways. Thanks for the video and lesson. Always something new and fun to learn at your channel. Thumbs up!!
I've got my Microsoft Certifications for Excel at different levels - THIS wasn't in any of it!!!! I'm loving these videos. I've just found your channel and have subbed. Thanks for sharing your knowledge with us. :D
After using excel for over 10 years, I shamefully admit that I’ve never got the grasp of the Index, Small and Rows function... I have tried many times to understand them but failed.. After watching your breakdown this nested formula and explaining every function step by step, I can gratefully say that I now understand these functions clearly.. this has to be the best tutorial explaining these functions... thank you soooo much for taking the time to explain..
Great tutorial! really enjoy watching your lessons. I feel that even though I am familiar with vba macro codes, those formulas really take me a few steps up in my excel skills.
Thanks! I'm very glad to help!) I must say that VBA and Worksheet Formulas are definitely two different animals and sometimes, I admit, I will just make a quick macro instead of a complex formula because, really, complex formula logic can be so crazy - that's also why I like to give you guys downloadable files, so you can just copy/paste as needed)
If it is simple values/numbers, can you put into the formula that the values be added together? I have a scenario where we have duplicate lookup's, and the duplicates found will just be added together as a single value. Your version has the value lookup and the results x3 in three separate cells. I will add, your version is the only one I have found that atleast finds all the duplicates and a result. I like it!
Hi.. saw your question. Here are two ways to return a partial match: =IFERROR(INDEX($B$2:$B$8,AGGREGATE(15,6,IF(FIND($D$2,$A$2:$A$8)>0,ROW($A$2:$A$8)-ROW($A$2)+1),ROWS(K$2:K2))),"") or =FILTER(B2:B8,IF(ISERROR(FIND(D2,A2:A8)),FALSE,TRUE)) I hope they help. Good luck!
Great Video, I really love this video, great formula, one question, I'm using or formula, and I have two values to search, not just one, I try to mix with other formulas but no luck, I'm not an expert, can you make a video with a formula like that?
Hello sir ..it's been wonderful watching your videos so far ..however I would request you to create a comprehensive videos from basics on PIVOT and Dashboard.. I couldn't find others UA-camrs videos as intriguing and understandable as I would have liked ...I badly need it ....would immensely appreciate it !! Shout out to Teach Excel in advance !!
Thank you!) And I do have the outline for a pivottable course but I am currently working in an intro to Macros course and it might be a while before I finish that one, as it's rather comprehensive. But, I will try to keep in mind the need for the other courses! Make sure to signup on teachexcel.com for the newsletter and then you will get a notification when I am starting the other courses and can provide feedback - I always take feedback when designing a course or new section.
@@TeachExcel sounds good ! Meanwhile I will bide my time for Macro intro..that must be interesting as well ..Also I am spreading a word far and wide ....for teachexcel.com
Thank you very much! I really appreciate that! I'm trying very hard to put more and more effort into this to make better content for everyone, and I really couldn't do it without the support!))
This is terrific! Thank you (as this answers an earlier question I posted). Now, to add on (and maybe bring in a lesson from last week...) let's say that the value in column A that you're matching in cell D2 is part of a longer string such as a Last Name that is part of a complete name (last, first, initial). And you can't break up the longer string. So, the value in D2 is just the last name, but the values in column A are last, first, middle. I was thinking that there might be some way to use a wildcard character but that would have to be in the array A1:A8 in your example. But I don't think a wild card can be used in an array... Is there some way that this type of formula can pick up part of a value in a range/array?
I think it actually was you who asked for that and then I looked and I couldn't believe that I didn't make this tutorial before, so thanks for prompting me to do it) As for the wildcard, I did think about this but then I realized that I already talk a lot so I decided I should not cover it here haha. And, before I do this next thing, I want to tell anyone who reads this that I do not normally answer Excel questions here, there is the forum for that www.teachexcel.com/talk/microsoft-office?src=yt_comment BUT, this is a fun little thing that I was able to quickly make, and so here is the updated version with the wildcard ability: =IFERROR(INDEX($B$2:$B$8, SMALL(IF(IFERROR(SEARCH($D$2,$A$2:$A$8)>0,FALSE), ROW($A$2:$A$8)-ROW($A$2)+1), ROW(1:1))),"") I did it very quickly so maybe it could be made better, but I'm actually a bit surprised by how easy the change was, only in the logical test section of the IF statement.
@@TeachExcel Thank you for your quick reply. Sorry to have not put this in the forum. I'm a long term learner with you, but first time really responding. I'll take it to the forum next time!
@@maryannepoatsy8599 You're very welcome! I didn't mind doing this formula quickie for you since it was easy and related to the video, but I included the note about the forum because people will view this video in the future, see that I answered a question, then try to ask a question here and I may never see the comment for the old video and then that's a bummer for everyone. For instance, I don't get notifications when people reply to my comments and I only saw your reply because I checked the video again - I don't think UA-cam is trying to foster a great Q&A system haha
Why not just make the macro run when whatever the formula points to changes? If you want better help, you can upload a sample file to the forum and ask there - a lot will depend on how everything is setup. www.teachexcel.com/talk/microsoft-office?src=yt_comment
Hi Kathy! This one is a tricky one for sure! If you don't understand this one, I promise to not hold it against you! If you have Excel 365, use the FILTER() function instead of this guy - my latest tutorial is on the FILTER() function and it makes life soooo much easier. Also, I do sometimes teach less complex stuff but I didn't know that anyone would find it interesting so I have been trending more complex. However, I am currently making a new batch of tutorials that are less crazy/complex topics now and if people like those then I will keep doing that. I must confess though, I do like giving complex tutorials haha so I will occasionally still make them ;)
Because the table could start in a lower row, say row 50, and then it wouldn't work. Try it out, moving the table down, and you will quickly see why you need the plus 1.
Most people who search for this will search for a "Vlookup" and the more technical that I get with the description, the more difficult that it will be for people to find this. This particular version of the "return all matches" lookup is the simplest and most versatile form that I use and it works just like a basic Vlookup or Index/Match and I mention early in the tutorial that it is a custom formula. There is no actual regular Vlookup function to perform this task as far as I am aware.
I've watched so many videos on the subject and this was by far the easiest. Easy to understand, well explained and made my life so much easier. Thank you!
Nice video! Here's a legacy formula version using AGGREGATE that does not require CSE: =IFERROR(INDEX($B$2:$B$8,AGGREGATE(15,6,(1/($A$2:$A$8=$D$2)*(ROW($A$2:$A$8)-ROW($A$2)+1)),ROWS(K$2:K2))),""). And of course now we can get all this done easily with the new FILTER function, as in: =FILTER(B2:B8,A2:A8=D2) which spills the results dynamically and does not require IFERROR. Fun to solve these in different ways. Thanks for the video and lesson. Always something new and fun to learn at your channel. Thumbs up!!
I've got my Microsoft Certifications for Excel at different levels - THIS wasn't in any of it!!!! I'm loving these videos. I've just found your channel and have subbed. Thanks for sharing your knowledge with us. :D
Brilliant! Just what I needed, thank you!
A classic requested by users.
Instead of ROW(1:1) also works ROW()-1. Thank you for the tutorial. Great!!!
You're very welcome!) For the ROW() function in this formula, that will only work if your formula starts in row 2, just to note.
After using excel for over 10 years, I shamefully admit that I’ve never got the grasp of the Index, Small and Rows function... I have tried many times to understand them but failed..
After watching your breakdown this nested formula and explaining every function step by step, I can gratefully say that I now understand these functions clearly.. this has to be the best tutorial explaining these functions... thank you soooo much for taking the time to explain..
That is an Amazing complement and I am really so happy that I was able to explain it in a way that you could understad :)))) You are very welcome :)
This formula is totally immaculate. Would you mind sharing how to display the data not in the rows, but in the columns?
This formula is pretty much what the whole Excel is all about.
Great tutorial! really enjoy watching your lessons. I feel that even though I am familiar with vba macro codes, those formulas really take me a few steps up in my excel skills.
Thanks! I'm very glad to help!) I must say that VBA and Worksheet Formulas are definitely two different animals and sometimes, I admit, I will just make a quick macro instead of a complex formula because, really, complex formula logic can be so crazy - that's also why I like to give you guys downloadable files, so you can just copy/paste as needed)
Many thanks for this fantastic tutorial. Amazing solution!
You're very welcome! I'm glad you like it)
Great Stuff..Loving These Complex Formulas,Thank You Sir :)
Glad you like it! I almost confused myself by the end of this one haha
Thanks for a useful Complex Formula with a well Explained...
You're very welcome! I'm glad you like how I explained it)
If it is simple values/numbers, can you put into the formula that the values be added together? I have a scenario where we have duplicate lookup's, and the duplicates found will just be added together as a single value. Your version has the value lookup and the results x3 in three separate cells. I will add, your version is the only one I have found that atleast finds all the duplicates and a result. I like it!
thank you man so much
Awesome ! Fortunate to have subscribed this.
So glad you like it))
Nice, thank you for sharing 👍
You're welcome! I'm glad you like it)
Very well explained
Thank you very much)))
Great! Thanks a lot!
Thanks! You're very welcome!!
Thank you Good Sir!!!
Thanks for the explanation. just one question: is it possible to search only part of the text such as * re *?
Hi.. saw your question. Here are two ways to return a partial match:
=IFERROR(INDEX($B$2:$B$8,AGGREGATE(15,6,IF(FIND($D$2,$A$2:$A$8)>0,ROW($A$2:$A$8)-ROW($A$2)+1),ROWS(K$2:K2))),"") or
=FILTER(B2:B8,IF(ISERROR(FIND(D2,A2:A8)),FALSE,TRUE))
I hope they help. Good luck!
Great Video, I really love this video, great formula, one question, I'm using or formula, and I have two values to search, not just one, I try to mix with other formulas but no luck, I'm not an expert, can you make a video with a formula like that?
If we type re only can we get the result?
how easily does this work when the information you are prompting to and for is in different spreadsheets
Hello sir ..it's been wonderful watching your videos so far ..however I would request you to create a comprehensive videos from basics on PIVOT and Dashboard.. I couldn't find others UA-camrs videos as intriguing and understandable as I would have liked ...I badly need it ....would immensely appreciate it !! Shout out to Teach Excel in advance !!
Thank you!) And I do have the outline for a pivottable course but I am currently working in an intro to Macros course and it might be a while before I finish that one, as it's rather comprehensive. But, I will try to keep in mind the need for the other courses! Make sure to signup on teachexcel.com for the newsletter and then you will get a notification when I am starting the other courses and can provide feedback - I always take feedback when designing a course or new section.
@@TeachExcel sounds good ! Meanwhile I will bide my time for Macro intro..that must be interesting as well ..Also I am spreading a word far and wide ....for teachexcel.com
Thank you very much! I really appreciate that! I'm trying very hard to put more and more effort into this to make better content for everyone, and I really couldn't do it without the support!))
may I ask you sir, how to output matching values not in one column , but in one row, in different worksheet?
really usefull for your videos. we want multy rows vlookup to return all matches in excel
Check my new videos and I think it should help)
This is terrific! Thank you (as this answers an earlier question I posted). Now, to add on (and maybe bring in a lesson from last week...) let's say that the value in column A that you're matching in cell D2 is part of a longer string such as a Last Name that is part of a complete name (last, first, initial). And you can't break up the longer string. So, the value in D2 is just the last name, but the values in column A are last, first, middle. I was thinking that there might be some way to use a wildcard character but that would have to be in the array A1:A8 in your example. But I don't think a wild card can be used in an array... Is there some way that this type of formula can pick up part of a value in a range/array?
I think it actually was you who asked for that and then I looked and I couldn't believe that I didn't make this tutorial before, so thanks for prompting me to do it)
As for the wildcard, I did think about this but then I realized that I already talk a lot so I decided I should not cover it here haha.
And, before I do this next thing, I want to tell anyone who reads this that I do not normally answer Excel questions here, there is the forum for that www.teachexcel.com/talk/microsoft-office?src=yt_comment
BUT, this is a fun little thing that I was able to quickly make, and so here is the updated version with the wildcard ability:
=IFERROR(INDEX($B$2:$B$8, SMALL(IF(IFERROR(SEARCH($D$2,$A$2:$A$8)>0,FALSE), ROW($A$2:$A$8)-ROW($A$2)+1), ROW(1:1))),"")
I did it very quickly so maybe it could be made better, but I'm actually a bit surprised by how easy the change was, only in the logical test section of the IF statement.
@@TeachExcel Thank you for your quick reply. Sorry to have not put this in the forum. I'm a long term learner with you, but first time really responding. I'll take it to the forum next time!
@@maryannepoatsy8599 You're very welcome! I didn't mind doing this formula quickie for you since it was easy and related to the video, but I included the note about the forum because people will view this video in the future, see that I answered a question, then try to ask a question here and I may never see the comment for the old video and then that's a bummer for everyone. For instance, I don't get notifications when people reply to my comments and I only saw your reply because I checked the video again - I don't think UA-cam is trying to foster a great Q&A system haha
Please make a video on how to run a macro when cell value changes by formula not by user manually.
Why not just make the macro run when whatever the formula points to changes? If you want better help, you can upload a sample file to the forum and ask there - a lot will depend on how everything is setup. www.teachexcel.com/talk/microsoft-office?src=yt_comment
I got lost, maybe when I have a clear head I will try again. Do you teach less complex Excel?
Hi Kathy! This one is a tricky one for sure! If you don't understand this one, I promise to not hold it against you! If you have Excel 365, use the FILTER() function instead of this guy - my latest tutorial is on the FILTER() function and it makes life soooo much easier.
Also, I do sometimes teach less complex stuff but I didn't know that anyone would find it interesting so I have been trending more complex. However, I am currently making a new batch of tutorials that are less crazy/complex topics now and if people like those then I will keep doing that. I must confess though, I do like giving complex tutorials haha so I will occasionally still make them ;)
It didn't work for me since my data was on a different sheet. Wonder why?! 🤔
Can you do this with the Xlookup function?
Probably. I just made a note to put it in the lineup for tutorials to make - seems like I will be doing a lot of lookup stuff in the next month hah.
Why wouldn’t you subtract 1, instead of subtracting ROW($A&2) and then adding 1 back in?
Because the table could start in a lower row, say row 50, and then it wouldn't work. Try it out, moving the table down, and you will quickly see why you need the plus 1.
TeachExcel Yeah that makes sense. I was coming it from the standpoint of data always starting in the second row.
How can we use it in command button?
I really don't know what you mean. This is not a macro, it's just a formula.
This is not Vlookup...... We can say lookup value return multiple results
Most people who search for this will search for a "Vlookup" and the more technical that I get with the description, the more difficult that it will be for people to find this.
This particular version of the "return all matches" lookup is the simplest and most versatile form that I use and it works just like a basic Vlookup or Index/Match and I mention early in the tutorial that it is a custom formula.
There is no actual regular Vlookup function to perform this task as far as I am aware.