Can't thank you enough! Since last 12 hrs, I was scratching my head to understand this and have gone through many articles and videos but couldn't get anything more clear! You have saved my day! Good luck!
Sumit Hello...I've been reading stuff online looking for the 2nd to Nth value lookup but your explanation was the best...I finally understood the logic behind both the Helper and the Array formulas...excellent job with the explanations...bless your soul...thank you.
Wow, this was SUPER helpful!! Thank you for making and posting this, it worked perfectly for what I needed. I used mine to populate a list vertically by counting the rows instead of columns, and it came through exactly as you showed otherwise. The helper column is genius! Thanks again!
sorry to be so off topic but does anyone know of a method to get back into an instagram account? I stupidly lost the account password. I love any assistance you can offer me.
Helper column or row is much easy to use and helpful. One thing we can do is to make the array into a table is by selecting the existing table array and press CTRL+T; so even when the data expands the formula with the helper column, automatically takes care of it. Problem with array is that if the data changes one can not delete any part of the array formula and has to do the things all over again. Fantastic :)
You Sir....are a savior and won a subscription. I had almost the same problem like the video you presented and was trying to figure it out, i thought that it was match, find or other non-sens and all this time it was old friend Vlookup....
The second formula is a bit complicated for me but I know it is largement applicated in many cases and apparently it is very useful. Thank you for your excellent explication !
Nice tutorial, unfortunately was not able to find this tutorial when I needed this I have used TRANSPOSE(FILTER) to do the same thing and it works like a charm.
I love your channel! Thank you so much. I don't know if it was discussed here, but now, with Excel 365, using the XLOOKUP function, you don't need to use the IFERROR function in the "helper column way".
Thank you for the great video. I was wondering if the reverse is possible. That is, the table on the right is available and make that into the table on the left with similar array formula
Good video for learning how to write long and complicated formulas. But the problem explained in this video can be resolved through Pivot Table without much fuss of any complicated formulas.
Sumitji, simply superb. Please suggest if it can be done using power query and n how? We would be eager to see your tutorial on this. Thanks in advance.
Thank you for this information but I want the vertically and can next row move down automatically after returning multiple duplicate values... Hope you'll get what I'm trying to say and respond 🙏
Really helpful. Get to know few shortcuts and particularly use of Columns() formula while concatenating with name in the Helper column was new to me. I keep playing around with Excel formulas and today got some good learning from your video. I hope i can use these learning in creating videos for my UA-cam channel Tech Reflections. Thanks for this helpful video.
Is there a way to do this between two dates? For example, in Column C, you add the dates next to each training that was done. In cells I1 and J1, you add the start and end dates, respectively. And then in E2 of your example, you add an element to the formula to find which training John did between the dates listed in I1 and J1.
You could have used a combination of transpose + filter + if formula rather than using an array formula which could have not even required iferror function and very easy to use. But explained really well. 👍👍
Hi thanks for the video it's really useful. Quick question for you though, when creating the helper column it is hardcoded to ...countif($A$2:A2,A2), is there a way to create an absolute reference to cell A2 using table referencing? It would be something like [@Name]&COUNTIF($A$2:[@Name],[@Name]), but i don't know how to reference A2, the first row and column in the table, using "table referencing".
Thank you so much. You did save me. I did it. But at 2:35 -36 around, do i need ctr shift enter every row function ? My one can't automatically be change.
Hello Sumit, thank you very very very much. It's exactly what I've been looking for. I mean the first part of the video. John1, John2, John3... I needed it for an interrupted sequence. Sumit, 1 question: Do you work for UpGrad?
Nice video, I am trying to display 2nd 3rd and nth occurance by using xlookup, but not able get 2nd occurance and so on, only displaying 1st occurance only, is there a way to find 2nd, 3rd and nth occurance?
I've fallen in love with you, been searching for ages for a simple explanation but no had one, thank you a whole bunch
🙈🙈 inenglish class will start on Wednesday evening 🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆 🌆
You are not Just excel expert but You are Excel magician... Great Job ,,,
This is by far the most easiest way to do it compared to so many links. Legendary
Can't thank you enough! Since last 12 hrs, I was scratching my head to understand this and have gone through many articles and videos but couldn't get anything more clear! You have saved my day! Good luck!
Sumit bansal not only a tutor but also a hope
Sumit Hello...I've been reading stuff online looking for the 2nd to Nth value lookup but your explanation was the best...I finally understood the logic behind both the Helper and the Array formulas...excellent job with the explanations...bless your soul...thank you.
Wow, this was SUPER helpful!! Thank you for making and posting this, it worked perfectly for what I needed. I used mine to populate a list vertically by counting the rows instead of columns, and it came through exactly as you showed otherwise. The helper column is genius! Thanks again!
sorry to be so off topic but does anyone know of a method to get back into an instagram account?
I stupidly lost the account password. I love any assistance you can offer me.
This is the best free software Ive seen. Respect.
Very crisply explained. Saved a ton of time surfing the solution over the internet.
Many thanks! You deserve my first comment on youtube!
I never thought I would learn something so useful from someone with Trump in their name...2020 keep giving.
I am watching your tutor video almost everyday , it helps me a lot thank you so much
Glad you're finding the videos useful :)
you are one and only the master of excel! i have no words how I can appreciate your work! thank you so much! you just saved my job thanks
I had the similar project recently. I just opened Trump Excel and I found this. I was like wow!!!! Thanks for posting it.
Thank you. Yoou are the king of kings in array formulas, super explanation.
Helper column or row is much easy to use and helpful. One thing we can do is to make the array into a table is by selecting the existing table array and press CTRL+T; so even when the data expands the formula with the helper column, automatically takes care of it. Problem with array is that if the data changes one can not delete any part of the array formula and has to do the things all over again. Fantastic :)
All your videos are crisp and easy to follow .
Thanks. Took a few minutes to understand the index formula and it works great.
You Sir....are a savior and won a subscription. I had almost the same problem like the video you presented and was trying to figure it out, i thought that it was match, find or other non-sens and all this time it was old friend Vlookup....
Bhai thank you...pura excel sheeka diya tumne ek video se....thank you from the bottom of my heart. May god bless you.
underrated skill in excel. thanks for sharing
Thank you so much!! This is exactly what I was looking for today and I got the solution. Keep up the good work!!
Very neat and simple explanation. That provided me the clue to the problem I have been searching to solve. Thanks a lot!
me too
Explained very clearly and concisely. Cheers
The second formula is a bit complicated for me but I know it is largement applicated in many cases and apparently it is very useful. Thank you for your excellent explication !
Thank you for explaining with ease. I was struggling so much earlier but you made it easy
Glad you found the video useful Shikha!
Awesome explanation....got to learn array function so easy....thanks so much 👏👏👏👏👏
Nice tutorial, unfortunately was not able to find this tutorial when I needed this I have used TRANSPOSE(FILTER) to do the same thing and it works like a charm.
Brilliant, really helpful. Thank you very much
Best explanation by far! Thanks!!!
Thanks You. I fall in difficulties in my project yours simple teaching method solve my big problem.
VERY USEFUL FORMULA FOR MULTIPLE TRAINING DONE BY EMPLOYEES
I love your channel! Thank you so much.
I don't know if it was discussed here, but now, with Excel 365, using the XLOOKUP function, you don't need to use the IFERROR function in the "helper column way".
Thanks bro..Ur formula for nth matching term helped me a lot in my office work..Keep teaching us this way
You're saving my life!
very good tutorial, excellent and simple explanation to understand... thanks
Very Very informative Lecture Sir
Very useful indeed. This tutorial can lead to so many usefulness
Glad you found the video useful!
Excellent, thanks for teaching us
Thank u for amazing videos.....it makes learning excel much simpler for beginners.
Very intelligently done excellent 👍👍👍
This was extremely helpful. Thank you for the step-by-step breakdown of nesting the formulas.
That array formula blew my mind. Thanks dude! Very helpful
Thank you so much.
Please keep it up you saved alot of time
Awesome 👍👍 It Works perfectly 💯 ...saved a lot of time..
Very clear explanation- Thank you so much.
Thank you for the great video. I was wondering if the reverse is possible. That is, the table on the right is available and make that into the table on the left with similar array formula
you saved my life...
I AM VERY VERY HAPPY AND USE FULL WITH YOUR CLASS
Great Video. Awesome Work. Thank you for sharing this valuable knowledge with us.
Thank you mate, very useful tips!
Glad you found the video helpful 🙂
Thank you so much for this tutorial! I gonna try it now!
it was life saving formula.. thank u so much
I'm helped a lot of your video. Thanks
Thanks dude. Great vid. Got me what I needed to do quickly and easily.
Hi. Thanks a ton for this video. It was very helpful and this was the solution to one of the problems I wanted to solve.
Good video for learning how to write long and complicated formulas. But the problem explained in this video can be resolved through Pivot Table without much fuss of any complicated formulas.
Amazingly well explained, clear and concise. Thank you so much! You are amazing
very well explained about the critical array formulas and root cause of errors also
wooooow.... This is sooooo helpful and the way you explained it is really awesome ..
Thanks for commenting Shilpa... Glad you found the video useful!
Excellent & amazing explanation. Keep up the good going. Thanks
VERY GRATEFUL SIR, EXACTLY WHAT I NEED
Thanks bro, you have safe my life today.
Extremely useful and very nicely explained
Excelent information thank you very much Highly appreciate your help
Wow. Really very helpful. thanks .
Sumitji, simply superb. Please suggest if it can be done using power query and n how? We would be eager to see your tutorial on this. Thanks in advance.
1st is good and easy but second is perfect😎❤
You are a genius
Good video....great explanation
2nd formula is use full for me thank you so much bro😍😍👏👏👌👌😘😘
You excelled the excell, can we put this in Data validation to see training1, training 2, training 3 in list format..?
Great stuff. Many thanks.
OMG OMG THANK YOU SO MUCH!! ♥️🙏🏼
Brilliant and helpful!
Works like a charm. Thank you so much.
Thank you for this information but I want the vertically and can next row move down automatically after returning multiple duplicate values... Hope you'll get what I'm trying to say and respond 🙏
Sir specific speach is super.
Very Helpful formula
Thanks....I was searching for this
Nice concept make it so easy
THANKS SIR
ITS VERY HELPFUL
Really helpful. Get to know few shortcuts and particularly use of Columns() formula while concatenating with name in the Helper column was new to me. I keep playing around with Excel formulas and today got some good learning from your video. I hope i can use these learning in creating videos for my UA-cam channel Tech Reflections. Thanks for this helpful video.
Is there a way to do this between two dates? For example, in Column C, you add the dates next to each training that was done. In cells I1 and J1, you add the start and end dates, respectively. And then in E2 of your example, you add an element to the formula to find which training John did between the dates listed in I1 and J1.
You could have used a combination of transpose + filter + if formula rather than using an array formula which could have not even required iferror function and very easy to use. But explained really well. 👍👍
Thank you bro, it's really helpful. You have explained it very well and easy to learn from you :)
Piviot table is the best to use in this example!
It is very helpful to my work
Hi thanks for the video it's really useful. Quick question for you though, when creating the helper column it is hardcoded to ...countif($A$2:A2,A2), is there a way to create an absolute reference to cell A2 using table referencing? It would be something like [@Name]&COUNTIF($A$2:[@Name],[@Name]), but i don't know how to reference A2, the first row and column in the table, using "table referencing".
Sir simply great 😄
what else is left now ...
u have made it so easy to deal with such type of nesting functions
The way explained is really awsome!, you rocked, thanks for your idea
Thank you so much. You did save me. I did it.
But at 2:35 -36 around, do i need ctr shift enter every row function ?
My one can't automatically be change.
No
That was really helpful and very well explained. Thank you.
Great info. Thanks so much, this helped me out of a jam!
Is it possible to add another column in between those? To add another set of information.
Hello Sumit, thank you very very very much. It's exactly what I've been looking for.
I mean the first part of the video. John1, John2, John3... I needed it for an interrupted sequence. Sumit, 1 question: Do you work for UpGrad?
Thank you , great work
Very nice, really helpful!!! Thanks a lot!!!
You are great!!!
Nice video, I am trying to display 2nd 3rd and nth occurance by using xlookup, but not able get 2nd occurance and so on, only displaying 1st occurance only, is there a way to find 2nd, 3rd and nth occurance?