Video není dostupné.
Omlouváme se.
Lookup and Find the 2nd, 3rd, or the Nth Matching Value in Excel
Vložit
- čas přidán 2. 08. 2024
- When it comes to looking up data in Excel, there are two amazing functions that I often use - VLOOKUP and INDEX (often in conjunction with the MATCH function).
However, these formulas are designed to find the first instance of the lookup value.
But what if you want to lookup the second, third, fourth or the Nth matching value.
Well, it's doable with a little bit extra work.
In this tutorial, I will show you various ways (with examples) on how to look up the second or Nth value in Excel.
-- Using Helper Column
-- Using Array Formula
Using helper column, you can create a unique lookup value. This can be done by appending the number of occurrence of a value in a list.
In this video, I explain using the employees and training done by them. You will learn how to find the first, second, or third training done by an employee using formulas (VLOOKUP and INDEX).
You can read more about it here + Example file: trumpexcel.com/lookup-second-...
Let's Connect:
Google+ ► plus.google.com/+Trumpexcel
Facebook ► / trumpexcel
Twitter ► / trumpexcel
Pinterest ► / trumpexcel
CZcams Channel: ► / trumpexcel #Excel #ExcelTips #ExcelTutorial
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 🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆🌆 🌆
This was extremely helpful. Thank you for the step-by-step breakdown of nesting the formulas.
Thank you so much!! This is exactly what I was looking for today and I got the solution. Keep up the good work!!
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
Excellent, thanks for teaching us
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 are not Just excel expert but You are Excel magician... Great Job ,,,
Amazingly well explained, clear and concise. Thank you so much! You are amazing
This is by far the most easiest way to do it compared to so many links. Legendary
Brilliant, really helpful. Thank you very much
That was really helpful and very well explained. Thank you.
Very crisply explained. Saved a ton of time surfing the solution over the internet.
Thanks bro..Ur formula for nth matching term helped me a lot in my office work..Keep teaching us this way
This is the best free software Ive seen. Respect.
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.
That array formula blew my mind. Thanks dude! Very helpful
The way explained is really awsome!, you rocked, thanks for your idea
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
Outstanding! Thank you so much for this!
Thank you very much!!!
I thought this was impossible to do. THANKS!
Great Video. Awesome Work. Thank you for sharing this valuable knowledge with us.
Excellent & amazing explanation. Keep up the good going. Thanks
Thank you bro, it's really helpful. You have explained it very well and easy to learn from you :)
You're the best, thank you so much!
very well explained about the critical array formulas and root cause of errors also
Works like a charm. Thank you so much.
Thanks. Took a few minutes to understand the index formula and it works great.
All your videos are crisp and easy to follow .
I am watching your tutor video almost everyday , it helps me a lot thank you so much
Glad you're finding the videos useful :)
Thanks bro, you have safe my life today.
I had the similar project recently. I just opened Trump Excel and I found this. I was like wow!!!! Thanks for posting it.
Very neat and simple explanation. That provided me the clue to the problem I have been searching to solve. Thanks a lot!
me too
Thank you so much for this tutorial! I gonna try it now!
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 !
very useful and informative... thank you so much for posting this video..
Thank you for explaining with ease. I was struggling so much earlier but you made it easy
Glad you found the video useful Shikha!
Thank you. Yoou are the king of kings in array formulas, super explanation.
Thank you so much, i've been struggling with this issue for some time. I defenetely use the array formula.
This is not array formula
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.
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".
Very clear explanation- Thank you so much.
awesome thank you very much
it's a great work
keep going
VERY GRATEFUL SIR, EXACTLY WHAT I NEED
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 CZcams channel Tech Reflections. Thanks for this helpful video.
Really great Sir,
It was amazing.. thanks A alot.
Thank u for amazing videos.....it makes learning excel much simpler for beginners.
underrated skill in excel. thanks for sharing
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....
Very nice, really helpful!!! Thanks a lot!!!
Thank you mate, very useful tips!
Glad you found the video helpful 🙂
Great info. Thanks so much, this helped me out of a jam!
you saved my life...
Extremely useful and very nicely explained
I AM VERY VERY HAPPY AND USE FULL WITH YOUR CLASS
Thanks, this was very helpful
Best explanation by far! Thanks!!!
Amazing! Helped me make my own invoice generating spreadsheet, thank you!
Thanks for commenting David... Glad you found the video useful!
I'm helped a lot of your video. Thanks
thanks for your reply, i have checked the uploading frequency of videos on your channel is more of excel stuff, i appreciate your knowledge and sample sources you have been releasing on channel. it would be more helpful to see more of vba part as well.
Thanks dude. Great vid. Got me what I needed to do quickly and easily.
You're saving my life!
very good tutorial, excellent and simple explanation to understand... thanks
Bhai thank you...pura excel sheeka diya tumne ek video se....thank you from the bottom of my heart. May god bless you.
Great stuff. Many thanks.
Thank you so much.
Please keep it up you saved alot of time
Excellent video as always and I have already employed the helper column method. My question is can this be done over more than one column? I am basically looking for a way to get excel to look at column A and Column B whilst tallying up the number of occurrences.
Awesome explanation....got to learn array function so easy....thanks so much 👏👏👏👏👏
Excelent information thank you very much Highly appreciate your help
Fantastic. Thank you for this
Brilliant and helpful!
Thanks....I was searching for this
That's cool and useful to me a lot.
Brother, thank you so much.
Thank you so much!!!! SO helpful!
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".
Very Nice
Very useful indeed. This tutorial can lead to so many usefulness
Glad you found the video useful!
Man you are good!!! Thanks
Very very thanks for the support
Thank you , great work
Wow. Really very helpful. thanks .
it was life saving formula.. thank u so much
OMG OMG THANK YOU 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.
Superb !!! thanks a lot....
Very intelligently done excellent 👍👍👍
what else is left now ...
u have made it so easy to deal with such type of nesting functions
You earned my subscription! You're amazing. Keep it up man! :)
Glad to have you as a subscriber :)
That was amazing.
Thank you!!!
Amazing work
THANK YOU SO MUCH.
Good video....great explanation
Thanks You. I fall in difficulties in my project yours simple teaching method solve my big problem.
You are great!!!
JUST SUPERB LOVE IT