Lookup and Find the 2nd, 3rd, or the Nth Matching Value in Excel

[@dstmusic9926]

I've fallen in love with you, been searching for ages for a simple explanation but no had one, thank you a whole bunch

[@mohdferoz124]

🙈🙈 inenglish class will start on Wednesday evening 🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆 🌆

[@sanketpatel8590]

You are not Just excel expert but You are Excel magician... Great Job ,,,

[@rups7vision]

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!

[@shibilsnoopy3973]

Sumit bansal not only a tutor but also a hope

[@MrShadowbuck]

This is by far the most easiest way to do it compared to so many links. Legendary

[@nghonchoi]

I am watching your tutor video almost everyday , it helps me a lot thank you so much

[@trumpexcel]

Glad you're finding the videos useful :)

[@JomyFrancis]

Very crisply explained. Saved a ton of time surfing the solution over the internet.

[@skullbreakerzone]

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

[@nishpakshdrishtikon6351]

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.

[@DoranAC]

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!

[@kasonnikolas7672]

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.

[@ajinkyashinde1778]

This is the best free software Ive seen. Respect.

[@saleemin27]

Thank you. Yoou are the king of kings in array formulas, super explanation.

[@acewin247]

Thanks. Took a few minutes to understand the index formula and it works great.

[@deeptiberiwal2583]

I had the similar project recently. I just opened Trump Excel and I found this. I was like wow!!!! Thanks for posting it.

[@Rionx]

Many thanks! You deserve my first comment on youtube!

[@sachinrv1]

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 :)

[@kirdevil]

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....

[@Pankaj-Verma-]

Bhai thank you...pura excel sheeka diya tumne ek video se....thank you from the bottom of my heart. May god bless you.

[@mahamohan1]

All your videos are crisp and easy to follow .

[@shangds75]

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 !

[@mardhikahan9553]

Very useful indeed. This tutorial can lead to so many usefulness

[@trumpexcel]

Glad you found the video useful!

[@jansiranisanthanakrishnan7103]

Very neat and simple explanation. That provided me the clue to the problem I have been searching to solve. Thanks a lot!

[@jayakrishnank4236]

me too

[@The28studio]

I never thought I would learn something so useful from someone with Trump in their name...2020 keep giving.

[@shikha_verma]

Thank you for explaining with ease. I was struggling so much earlier but you made it easy

[@trumpexcel]

Glad you found the video useful Shikha!

[@martinry5412]

underrated skill in excel. thanks for sharing

[@MrRoosterali]

Brilliant, really helpful. Thank you very much

[@redbaron1901]

Explained very clearly and concisely. Cheers

[@shrikantdamle7244]

VERY USEFUL FORMULA FOR MULTIPLE TRAINING DONE BY EMPLOYEES

[@basusubhadeep1]

Thank you so much!! This is exactly what I was looking for today and I got the solution. Keep up the good work!!

[@hritikasinha7627]

Thank u for amazing videos.....it makes learning excel much simpler for beginners.

[@joydeepmondal12]

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.

[@Saatt79]

Awesome explanation....got to learn array function so easy....thanks so much 👏👏👏👏👏

[@anandv.k.7238]

very good tutorial, excellent and simple explanation to understand... thanks

[@prabhakaran.s9723]

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

[@ruigsn]

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".

[@abdulazizqureshi2886]

Very Very informative Lecture Sir

[@shubhamyadav9708]

Thanks bro..Ur formula for nth matching term helped me a lot in my office work..Keep teaching us this way

[@sabelozwane6917]

That array formula blew my mind. Thanks dude! Very helpful

[@amandaseif8648]

Best explanation by far! Thanks!!!

[@willwright3021]

You're saving my life!

[@fernando5166]

Excellent, thanks for teaching us

[@pakistankayrangaakaysung]

Thanks You. I fall in difficulties in my project yours simple teaching method solve my big problem.

[@shilpasinha100]

wooooow.... This is sooooo helpful and the way you explained it is really awesome ..

[@trumpexcel]

Thanks for commenting Shilpa... Glad you found the video useful!

[@rajdhanraj2]

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 🙏

[@alissadale42]

This was extremely helpful. Thank you for the step-by-step breakdown of nesting the formulas.

[@RoadiesErGautam]

Awesome 👍👍 It Works perfectly 💯 ...saved a lot of time..

[@fayemelo8573]

Is it possible to add another column in between those? To add another set of information.

[@PrinceYadav-xk5sp]

Thank you so much. Please keep it up you saved alot of time

[@prakashsrinivasan7840]

Very intelligently done excellent 👍👍👍

[@AyessaJoyce]

you saved my life...

[@pinjalgajjar718]

You excelled the excell, can we put this in Data validation to see training1, training 2, training 3 in list format..?

[@laljha149]

very well explained about the critical array formulas and root cause of errors also

[@shanoobolavanna]

I AM VERY VERY HAPPY AND USE FULL WITH YOUR CLASS

[@ytbeLaw]

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.

[@amila247]

Hi Sumith, need to find next larger or similar value if that value hasn’t been taken in the list by the values above, do you have a solution pls.

[@captdevendra]

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.

[@rashekade7703]

Very clear explanation- Thank you so much.

[@m-tech2864]

it was life saving formula.. thank u so much

[@abcshr]

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?

[@martinry5412]

Btw, can you find the 1st and 2nd matching value based on another column? like it pinpoints John and search for the specific training (ie. Excel).

[@alliyadav2570]

Will it work for looku up values which are numeric Or Alphanumaric pls. Show exaple.

[@Pankaj-Verma-]

Great Video. Awesome Work. Thank you for sharing this valuable knowledge with us.

[@onlinetricks8758]

I'm helped a lot of your video. Thanks

[@rajeevgosavi4514]

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.

[@greentail]

Thank you so much for this tutorial! I gonna try it now!

[@HoRRoRlets]

Thanks dude. Great vid. Got me what I needed to do quickly and easily.

[@andrescarrasquero7429]

Thank you mate, very useful tips!

[@trumpexcel]

Glad you found the video helpful 🙂

[@debonil95]

If in place of excel or word, numbers are given then how would you get the sum of the numbers.

[@casper3842]

can i use this in drop down list?

[@shahidpathan4016]

Extremely useful and very nicely explained

[@vaibhaviyadav9402]

How to use index match formula if the text in column B like excel, powerpoint,word are in merged cells?

[@MrSimoneJJ]

Any ideas about doing the same thing on a larger data set ? Or on a data set of which the number of occurrences cannot be established a priori?

[@dennisdare212]

I like this function but it doesn't work if you are in table format and trying to use an entry form. Any suggestions?

[@juanjcampos3985]

Excelent information thank you very much Highly appreciate your help

[@priyankaparida8187]

Hi, Thank you for the video. Need your help in "How will row function give you numbers from 2 to 14? Since A2 if frozen inside row function, it will always return 2.

[@invenew]

Great video ... could you help in how the final table ( after using the formulas) could be COUNTA, i.e., count the word text ; "Excel", "Power point" and "Word" resulted in the final table due to using FORMULAS. as COUNTA doesn't work, COUNTIF has no certain criteria could be applied ... Thank you in advance

[@MishMashFurniture]

Hi Sumit Sir, Can you please help to put tab (with formula) in VBA

[@TH2023-l3n]

Amazingly well explained, clear and concise. Thank you so much! You are amazing

[@Toxic_-_James]

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".

[@yoganagapurkar631]

What would be the formula if I want to run the formula of helper vertically (row).. Will the formula change in place of column.

[@mohitbhatnagar5647]

Pls help me how to find data in vertical format rather than horizontal format. I means if we have to find lookup value in vertical straight line then how will we do?? Pls help

[@mohitbhatnagar5647]

In simple word pls help how to find source data as it is in another file through such index formula you described.

[@jazibjawaid9773]

Thanks bro, you have safe my life today.

[@razamoulvi8801]

2nd formula is use full for me thank you so much bro😍😍👏👏👌👌😘😘

[@ckhara]

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.

[@jessmyers2740]

Formula is only working on the first cell. When I drag down the column or across the row it is giving the same answer as the first. Does this have anything to do with my sheet being numerical values opposed to alpha?

[@phanindrakumarvavilala804]

Excellent & amazing explanation. Keep up the good going. Thanks

[@aykumar26]

Good video....great explanation

[@bikasdas5441]

VERY GRATEFUL SIR, EXACTLY WHAT I NEED

[@christopherroxas23]

Hi! thanks for the video. What if i just wanted to find the matching value before the last? ex. if there are 100 matching value, i need to get the 99th value. thank you

[@venusseth305]

How do I look up the last entered value if I don't want to use a number to it?

[@melissaslivka654]

If you had a column with a grade after the training and wanted to have that information pull into another sheet. how would you pull johns grades for each class?

[@vinayakpotadar4093]

Sir specific speach is super.

[@MyPanish]

Wow. Really very helpful. thanks .

[@rajasami7441]

You are a genius

[@TechReflections]

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 KZbin channel Tech Reflections. Thanks for this helpful video.

[@mortenbremelhoej2000]

If there is an infinite number of matching values, is it possible to return the values in one cell - comma separated?

[@ConsulthinkProgrammer]

Yes. You Can use google sheets to handle thats problem. Vlookup return Multiple Value - Multi Index - Multi Rows (Multi Baris) And Multi Columns (Multi Kolom) kzbin.info/www/bejne/mKCWgIigm76Mj6M

[@veluji8710]

While i use array function when i change source data then automatically change or not

[@hemantchambhare60]

What if I want to have all the 3 trainings done to be printed in single cell in multiple rows instead of them being in different cell as you've shown above??

[@womanwoman3876]

Hi, in my data i have many purchase orders for different products, i have a stock number for each product bbut the problem is i want my stock number to be subtracted from the previous purchase order according to date, i want the net stock, but it means i need stock number to be subtracted from the previous date purchase order, any help?

[@rajanikant5126]

Hi I need a formula for data validation. "A" Contains Emplyee Nos and "B" Contains date of their tours. I need a validation for "B" to restrict same employee putting multiple tours for a single day. Please help.