Use VLOOKUP to Find the 2nd Match (or nth Match)
If you are familiar with the VLOOKUP function it's a great tool to lookup values in a range or table. However it will only bring back the first instance of the looked up value. What if you wanted the second instance of the value or even have all the instances of the value brought back? This could be done by using a helper column and the COUNTIF function. Watch the video to see how it's done!
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~
Пікірлер: 267
For more videos that cover lookup concepts see the playlist at kzread.info/head/PL-n8f1cY_Qw95JHWjCjPofsCd7hCWjI8l
@DougHExcel
4 жыл бұрын
...
Just what I need, and such an immaculate, lucid explanation. Great job, man! Thanks a lot for this!!!
@DougHExcel
5 жыл бұрын
Hi Stan McFoster, glad you liked it, thanks for commenting!
Thank you very much. Simple explanation and very useful too.
@DougHExcel
6 жыл бұрын
+Jagadish Kumar you're welcome!
watched at least 10 videos to help me find a solution to the problem!Yours was the most helpful ,clear and concise!Keep doing what you are doing and Thanks a TON
@DougHExcel
3 жыл бұрын
Glad it helped!
That was really easy to understand, made my current excel project at least 2 times lighter, and easy to work on, thank you very much.
@DougHExcel
6 жыл бұрын
Thanks Felipe Vieira, glad it helped!
Thanks! I was looking for something like this the whole day! Subscribed!
@DougHExcel
6 жыл бұрын
Hi Francis Ilustre...you're welcome, glad you liked!
Superb! Very useful and clear explanation. Thanks!
@DougHExcel
5 жыл бұрын
Hi David Toh, glad you liked it, thanks for commenting!
Nicely done, Doug! Thanks.
@DougHExcel
7 жыл бұрын
Hi Wilhelm van der Merwe...you're welcome, glad you liked!
Your video is really so well-explained. Enjoy it a lot. Thanks!
@DougHExcel
7 жыл бұрын
Hi Vakies, glad you liked it, thanks for commenting!
This is an excellent tutorial! Thanks!!
@DougHExcel
5 жыл бұрын
Hi Heather Simmons, glad you liked it, thanks for commenting!
Really helpful and simple method. Thank you so much
@DougHExcel
6 жыл бұрын
Hi prakash kumar chandra sekeran, glad you liked it, thanks for commenting!
Brilliant, thank you Doug!
@DougHExcel
Жыл бұрын
You’re welcome, thanks for the comment!
THANK YOU SO MUCH!!!! I've been searching this strategy for so long :)
@DougHExcel
6 жыл бұрын
Hi RJ Gasing...you're welcome, glad you liked!
Doug, you are a life saver. Great video and extremely explanatory. I think you should expand this video to look up all values for SKU at once. Incorporating the helper column may confuse a few people.
@DougHExcel
6 жыл бұрын
Hi vishal shah, glad you liked it, thanks for commenting!
@tejaswaroop920
4 жыл бұрын
Yes waiting for what you have asked if someone can help to lookup all values that would be the greatest one too.
Cool trick. Thanks Doug !
@DougHExcel
7 жыл бұрын
You're Welcome!
Excellent video and formula usage!
@DougHExcel
5 жыл бұрын
Hi Paul Flood, glad you liked it, thanks for commenting!
This is so fire. I love you a lot ngl. Thank you for this
@DougHExcel
5 жыл бұрын
Hi Cruzznce, glad you liked it, thanks for commenting!
Great idea. Thanks. That Countif formula is genius.
@DougHExcel
5 жыл бұрын
Hi Resz7, glad you liked it, thanks for commenting!
This is what I was looking for. Thanks !
@DougHExcel
Жыл бұрын
You’re welcome!!
Nice one Doug, Thank you.
@DougHExcel
7 жыл бұрын
You're Welcome!
Awesome, thanks for this mate!
@DougHExcel
3 жыл бұрын
Sure thing!
Great logic. Thanks for the video!!
@DougHExcel
6 жыл бұрын
Hi Kshitiz Jain, glad you liked it, thanks for commenting!
How generous you are. You helped me a lot. Thank you, sir.
@DougHExcel
4 жыл бұрын
Happy to help
Explained clearly great job thanks sir
@DougHExcel
5 жыл бұрын
You’re welcome!
Nice Trick Doug !! Thank You
@DougHExcel
7 жыл бұрын
You're Welcome!
This is soooo useful!! thank you
@DougHExcel
5 жыл бұрын
Glad you liked!
Great work 👍 thanks 😊
@DougHExcel
3 жыл бұрын
Thank you too!
Thanks so much. So clear and useful!
@DougHExcel
6 жыл бұрын
Hi shirabatya, glad you liked it, thanks for commenting!
Thank you so much for this . This was exactly the workaround I was looking for my adhoc report.
@DougHExcel
3 жыл бұрын
Great to hear!
Awesome! This just saved me so much time! Thank you!
@DougHExcel
3 жыл бұрын
Great to hear!
Fantastic advise! I really appreciate this.
@DougHExcel
4 жыл бұрын
So glad!
Muito bom Doug, grande dica. Obrigado.
@DougHExcel
7 жыл бұрын
De nada. Obrigado!
This help me a lot. Thank you very much
@DougHExcel
4 жыл бұрын
Thanks Sakshi Waikar, glad it helped!
Great tip...thx for sharing
@DougHExcel
5 жыл бұрын
You're Welcome!
The best! Thank you so much! 🥰
@DougHExcel
3 жыл бұрын
You’re welcome 😊
Brilliant! Simple solution to a complicated problem
@DougHExcel
6 жыл бұрын
Hi kjbarks, glad you liked it, thanks for commenting!
Excellent work very helpful
@DougHExcel
4 жыл бұрын
Thanks ABU BAKAR!
This is great! What's funny is, I was capable of thinking all the way up to designing my own key (using the ampersand), but just didn't know how to make the key unique with the cool countif trick. Now, I can pull all of my unique occurrences for each occurrence. Thank you!
@DougHExcel
5 жыл бұрын
Hi Tessitura9, glad you liked it, thanks for commenting!
peoples cleverness and generosity amazes me , always. thanks
@DougHExcel
Жыл бұрын
You’re welcome!
Amazing, thank you. You really helped me
@DougHExcel
6 жыл бұрын
Thanks Faisal Alrashidi, glad it helped!
Thanks very much, its very useful
@DougHExcel
5 жыл бұрын
You’re welcome
Great stuff, gonna test it tomorrow
@DougHExcel
4 жыл бұрын
Hi Andy Przybysz, thanks for the comment!
Thank you for sharing😊. Good trick.
@DougHExcel
7 жыл бұрын
Hi ARKS Production, thanks for the comment!
Brilliant idea to to create helper table, thank you for this tip..
@DougHExcel
5 жыл бұрын
Thanks hitesh chand!
It helps my current project, thanks !
@DougHExcel
6 жыл бұрын
Hi Adrian Kristanto...you're welcome, glad you liked!
Thanks so much for this! :)
@DougHExcel
3 жыл бұрын
You're so welcome!
Very useful for old Vlookup guy like me with a helper column. Thanks
@DougHExcel
3 жыл бұрын
Great to hear!
Brilliant. Thank you
@DougHExcel
6 жыл бұрын
Hi Ayanda Mfeka...you're welcome, glad you liked!
Thanks sir, this videos very helfull for my daily wark tysm.
@DougHExcel
5 жыл бұрын
You’re welcome!
This is awesome ❤
@DougHExcel
9 ай бұрын
Hi Will M, thanks for the comment!
Nice Video Doug H Bro....... Thanks
@DougHExcel
6 жыл бұрын
+Sayyed Waseem you're welcome!
really helpful, thank you
@DougHExcel
6 жыл бұрын
You're Welcome!
Thank you - made my day
@DougHExcel
2 жыл бұрын
You’re welcome!
thanks for making it easier. Thanks a lot for this video
@DougHExcel
5 жыл бұрын
You’re welcome!
Thanks Doug very interesting
@DougHExcel
7 жыл бұрын
Hi Mario Limoges, glad you liked it, thanks for commenting!
Thank you so much , this will help me a lot .
@DougHExcel
5 жыл бұрын
Thanks Ehab Monther, glad it helped!
Thanks man...u saved my time.
@DougHExcel
4 жыл бұрын
Glad I could help
Tanq bro...very use full information
@DougHExcel
6 жыл бұрын
You're Welcome!
Excellent working skill. It will very helpful to solve large database problems.
@DougHExcel
4 жыл бұрын
Glad you think so!
THANK YOU! 🙏🏼
@DougHExcel
3 жыл бұрын
You’re welcome!
You are my hero, I have been trying to get this to work for ages, yours is the easiest method
@DougHExcel
4 жыл бұрын
Glad you liked it!
Thank you!
@DougHExcel
3 жыл бұрын
You’re welcome
thank you, you save my time!
@DougHExcel
3 жыл бұрын
Glad it helped!
it's very usefull trick.thanks.
@DougHExcel
7 жыл бұрын
Hi bohemaxxtum...you're welcome, glad you liked!
Very cool.. gonna have to save this one to keep it handy.. never needed to do that, but you never know right.
@DougHExcel
7 жыл бұрын
Hi nancmadi, glad you liked it, thanks for commenting!
Great Video.
@DougHExcel
5 жыл бұрын
Hi Pankaj Verma, thanks for the comment!
Thank you Boss...
@DougHExcel
4 жыл бұрын
You're Welcome!
This really solved my problem - Very good illustration
@DougHExcel
4 жыл бұрын
Thanks Vijay Kumar, glad it helped!
Great trick thank you
@DougHExcel
6 жыл бұрын
You're Welcome!
Thanks for the nth fun!!!
@DougHExcel
7 жыл бұрын
Thanks Mike!
Bro, this technique is very good. It solved my problem. Thanks a ton.
@DougHExcel
5 жыл бұрын
Thanks Gurudatt Nayak, glad it helped!
Tx Very much, its realy help me so much
@DougHExcel
6 жыл бұрын
Thanks Anas Mustari, glad it helped!
Thank you from Egypt :D
@DougHExcel
4 жыл бұрын
You're Welcome!
thanks this really help m
@DougHExcel
4 жыл бұрын
You're Welcome!
Thanks!
@DougHExcel
4 жыл бұрын
You're Welcome!
Great solution, :)
@DougHExcel
7 жыл бұрын
Hi Dritan Naum, glad you liked it, thanks for commenting!
Thanks for share.
@DougHExcel
7 жыл бұрын
You're Welcome!
Thank you so much Bro :)
@DougHExcel
6 жыл бұрын
You're Welcome!
i am very much thankful to you, it helps me a lot to solve my problems..................hats of to you..............My Name is Wasim Raja from INDIA
@DougHExcel
6 жыл бұрын
Hi wasim raja, glad you liked it, thanks for commenting!
great stuff..
@DougHExcel
7 жыл бұрын
Hi Vimal Saxena, glad you liked it, thanks for commenting!
THANK U SO MUCH BRO👍😇🤝
@DougHExcel
7 жыл бұрын
You're Welcome!
Thank you very much, the helper column is a great idea. I found half of the solution im looking for, but now I need to figure out how to make the LOOKUP value change after it finds all the possible results and moves to the next row to do another VLOOKUP. (Basically it SORTS the results from top to bottom, but without touching the filtering or sorting buttons.) Wish me luck !
@DougHExcel
Жыл бұрын
Maybe the FILTER function can help. Perform a Lookup with the FILTER Function kzread.info/dash/bejne/op6ml8WlmtHcqLg.html
Thank you..
@DougHExcel
5 жыл бұрын
You're Welcome!
You are great
@DougHExcel
5 жыл бұрын
Hi Income Tax, thanks for the comment!
Brilliant
@DougHExcel
6 жыл бұрын
Thanks for the comment!
Salute!!!
@DougHExcel
3 жыл бұрын
Hi Backend Admin, thanks for the comment!
THANKS
@DougHExcel
6 жыл бұрын
You're welcome!
This is brilliant! Save me from wrapping my head around the 'Small' formula 🤭👍
@DougHExcel
3 жыл бұрын
So glad!
Thanks for the video. It helped me alot. But the value did not change over "H2". How can I fix it?
If you wanted to skip the helper column altogether, you could always combine with SMALL function as it works with both numbers and text as a nth match function. I use it with INDEX and MATCH frequently.
@DougHExcel
3 жыл бұрын
Hi That Guy, thanks for the comment!
solved it very easily.. thanks alot.
@DougHExcel
5 жыл бұрын
You're Welcome!
Hi, this works great. How would I show data based on two drop down boxes
@terryfoster4104
6 жыл бұрын
For example, the first drop down is district managers and that shows me all the hotels in their area. The second is for maintenance managers but they don't cover all the district managers hotels so if I select both drop down I only want to see hotels coveted by both district manager and maintenance manager. Hope it makes sense
@DougHExcel
3 жыл бұрын
maybe one of these can help kzread.infosearch?query=multi%20lookup
Thanks for this lesson! What if there is more columns and I want data from complete raw with different values to match?
@DougHExcel
4 жыл бұрын
Hi Normunds Broks, thanks for the comment! You may want to consider Power Query for lookup too kzread.infosearch?query=query+lookup
@normundsbroks4595
4 жыл бұрын
Doug H Thanks a lot for idea, but unfortunatelly it is not what can solwe my case. May I ask you in private msg. just your opinion what could be the right function to solve my problem? I will find solution myself, but I really need just direction. 😏
Hi Doug. How would you use the formula to always return the last match of multiple matches? Trying to do a formula where many different students will be submitting information under their name multiple times through google forms, and their most recent submission is the one that I need the vlookup function to display (for all 101 students). Would be awesome if you can help out with this!
@DougHExcel
5 жыл бұрын
Maybe this will help kzread.info/dash/bejne/paak09GskrzOpcY.html
Thanks, simple and easy. Just remove the #N/A with its proper function and it will keep the spreadsheet totally clean.
@DougHExcel
4 жыл бұрын
Hi Manuel Mata Rueda, thanks for the comment!
For example, the first drop down is district managers and that shows me all the hotels in their area. The second is for maintenance managers but they don't cover all the district managers hotels so if I select both drop down I only want to see hotels coveted by both district manager and maintenance manager. Hope it makes sense
@DougHExcel
6 жыл бұрын
This is like a lookup with multiple criteria. See these for some insights => kzread.info/dash/bejne/a3WE1syRdb3gptY.html kzread.info/dash/bejne/kYGEksx9o5u-pLg.html kzread.info/dash/bejne/jHqJxa6Lns-oqbw.html
what does the quotation mark " " in "A18 used for ? I am still a little bit clueless in this. thanks
@DougHExcel
5 жыл бұрын
when you concatenate a string/text like the colon, it needs to be enclosed in double quotes
I have article wise pull back details if i sacnning in excel sheet vlookup need find find how many left from article list
@DougHExcel
3 жыл бұрын
Sorry, don’t understand the comment/ question...
Hi, great video, thank you. However, i have a question, how to change your formula to look by the keyword. I have a list where i need to vlookup only cells with specific word.
@DougHExcel
4 жыл бұрын
See if any of these fit kzread.infosearch?query=power+query+search
What if I have a list with multiple rows and columns that I need for a particular constant only? For example: I have a list of signed contracts with the estimator's name, date of signed contract, contract #, customer name, etc. I want to pull only the contracts for a particular estimator on another sheet without showing him any of the other estimators' signed contracts. The table it pulls the info from will constantly be updated with new signed contracts and some signed contracts that have been scheduled/removed from the list. How do I accomplish this?
@DougHExcel
6 жыл бұрын
Try using either adv. filter kzread.info/dash/bejne/dYVpydCHd5PalbQ.html or auto filter kzread.info/dash/bejne/inhkr6qooq7bhbg.html