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

[@DougHExcel]

For more videos that cover lookup concepts see the playlist at ruclips.net/p/PL-n8f1cY_Qw95JHWjCjPofsCd7hCWjI8l

[@DougHExcel]

...

[@jagadishkumar2581]

Thank you very much. Simple explanation and very useful too.

[@DougHExcel]

+Jagadish Kumar you're welcome!

[@MrCromler]

Just what I need, and such an immaculate, lucid explanation. Great job, man! Thanks a lot for this!!!

[@DougHExcel]

Hi Stan McFoster, glad you liked it, thanks for commenting!

[@felipevieira3906]

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]

Thanks Felipe Vieira, glad it helped!

[@zahiissam]

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]

Glad it helped!

[@Vakies]

Your video is really so well-explained. Enjoy it a lot. Thanks!

[@DougHExcel]

Hi Vakies, glad you liked it, thanks for commenting!

[@Tessitura9]

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]

Hi Tessitura9, glad you liked it, thanks for commenting!

[@vishalshah1968]

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]

Hi vishal shah, glad you liked it, thanks for commenting!

[@tejaswaroop920]

Yes waiting for what you have asked if someone can help to lookup all values that would be the greatest one too.

[@Prakashkumar-cp4mv]

Really helpful and simple method. Thank you so much

[@DougHExcel]

Hi prakash kumar chandra sekeran, glad you liked it, thanks for commenting!

[@King1CurtisLOLZBros]

This is so fire. I love you a lot ngl. Thank you for this

[@DougHExcel]

Hi Cruzznce, glad you liked it, thanks for commenting!

[@francisilustre3645]

Thanks! I was looking for something like this the whole day! Subscribed!

[@DougHExcel]

Hi Francis Ilustre...you're welcome, glad you liked!

[@zniu_wilhelmvandermerwe7247]

Nicely done, Doug! Thanks.

[@DougHExcel]

Hi Wilhelm van der Merwe...you're welcome, glad you liked!

[@heathersimmons6164]

This is an excellent tutorial! Thanks!!

[@DougHExcel]

Hi Heather Simmons, glad you liked it, thanks for commenting!

[@davtoh]

Superb! Very useful and clear explanation. Thanks!

[@DougHExcel]

Hi David Toh, glad you liked it, thanks for commenting!

[@reszzz]

Great idea. Thanks. That Countif formula is genius.

[@DougHExcel]

Hi Resz7, glad you liked it, thanks for commenting!

[@MuscularMetal666]

How generous you are. You helped me a lot. Thank you, sir.

[@DougHExcel]

Happy to help

[@rjmaratas6921]

THANK YOU SO MUCH!!!! I've been searching this strategy for so long :)

[@DougHExcel]

Hi RJ Gasing...you're welcome, glad you liked!

[@Mossadzz]

Brilliant, thank you Doug!

[@DougHExcel]

You’re welcome, thanks for the comment!

[@Victor-ol1lo]

Cool trick. Thanks Doug !

[@DougHExcel]

You're Welcome!

[@kjbarks]

Brilliant! Simple solution to a complicated problem

[@DougHExcel]

Hi kjbarks, glad you liked it, thanks for commenting!

[@ajinkyavaidya7129]

This is what I was looking for. Thanks !

[@DougHExcel]

You’re welcome!!

[@tangben9297]

Thank you so much for this . This was exactly the workaround I was looking for my adhoc report.

[@DougHExcel]

Great to hear!

[@Kildergcowboy]

Excellent video and formula usage!

[@DougHExcel]

Hi Paul Flood, glad you liked it, thanks for commenting!

[@Luciano_mp]

Muito bom Doug, grande dica. Obrigado.

[@DougHExcel]

De nada. Obrigado!

[@katarina2822]

This is soooo useful!! thank you

[@DougHExcel]

Glad you liked!

[@shayb615]

Awesome! This just saved me so much time! Thank you!

[@DougHExcel]

Great to hear!

[@SunilSingh-ee4sc]

Explained clearly great job thanks sir

[@DougHExcel]

You’re welcome!

[@aushutchy]

Awesome, thanks for this mate!

[@DougHExcel]

Sure thing!

[@michaelfullard4983]

Nice one Doug, Thank you.

[@DougHExcel]

You're Welcome!

[@excelisfun]

Thanks for the nth fun!!!

[@DougHExcel]

Thanks Mike!

[@automationguide3498]

Nice Trick Doug !!
Thank You

[@DougHExcel]

You're Welcome!

[@WehibMohammedYusuf]

peoples cleverness and generosity amazes me , always. thanks

[@DougHExcel]

You’re welcome!

[@andree010]

Great stuff, gonna test it tomorrow

[@DougHExcel]

Hi Andy Przybysz, thanks for the comment!

[@sakshiwaikar4431]

This help me a lot. Thank you very much

[@DougHExcel]

Thanks Sakshi Waikar, glad it helped!

[@shirabatya]

Thanks so much. So clear and useful!

[@DougHExcel]

Hi shirabatya, glad you liked it, thanks for commenting!

[@ben9879]

It helps my current project, thanks !

[@DougHExcel]

Hi Adrian Kristanto...you're welcome, glad you liked!

[@kshitizjain6384]

Great logic. Thanks for the video!!

[@DougHExcel]

Hi Kshitiz Jain, glad you liked it, thanks for commenting!

[@sunilpawar4146]

Thanks sir,
this videos very helfull for my daily wark tysm.

[@DougHExcel]

You’re welcome!

[@AbuBakar-hv2em]

Excellent work very helpful

[@DougHExcel]

Thanks ABU BAKAR!

[@nagazatowodn5442]

The best! Thank you so much! 🥰

[@DougHExcel]

You’re welcome 😊

[@trimssr]

Fantastic advise! I really appreciate this.

[@DougHExcel]

So glad!

[@swapnilbhostekar7186]

Great work 👍 thanks 😊

[@DougHExcel]

Thank you too!

[@thefaisal13]

Amazing, thank you. You really helped me

[@DougHExcel]

Thanks Faisal Alrashidi, glad it helped!

[@nadeermusthafa2847]

Thanks man...u saved my time.

[@DougHExcel]

Glad I could help

[@hiteshchand]

Brilliant idea to to create helper table, thank you for this tip..

[@DougHExcel]

Thanks hitesh chand!

[@anazi]

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
ruclips.net/video/pjr5cljnvxU/видео.html

[@diptisharma9900]

thanks for making it easier.
Thanks a lot for this video

[@DougHExcel]

You’re welcome!

[@asadmahadi628]

Very useful for old Vlookup guy like me with a helper column. Thanks

[@DougHExcel]

Great to hear!

[@EhabMonther]

Thank you so much , this will help me a lot .

[@DougHExcel]

Thanks Ehab Monther, glad it helped!

[@oliviermanbode4788]

Great tip...thx for sharing

[@DougHExcel]

You're Welcome!

[@1KSO7EC409]

Thank you - made my day

[@DougHExcel]

You’re welcome!

[@sudhamoymahapatra1971]

Excellent working skill. It will very helpful to solve large database problems.

[@DougHExcel]

Glad you think so!

[@allenlin4510]

thank you, you save my time!

[@DougHExcel]

Glad it helped!

[@Keizerin]

Thanks so much for this! :)

[@DougHExcel]

You're so welcome!

[@SPARK9026ONLINE]

Thank you for sharing😊. Good trick.

[@DougHExcel]

Hi ARKS Production, thanks for the comment!

[@nancmadi]

Very cool.. gonna have to save this one to keep it handy.. never needed to do that, but you never know right.

[@DougHExcel]

Hi nancmadi, glad you liked it, thanks for commenting!

[@mohammedhamandi3500]

Thanks very much, its very useful

[@DougHExcel]

You’re welcome

[@gdn1983]

Bro, this technique is very good. It solved my problem. Thanks a ton.

[@DougHExcel]

Thanks Gurudatt Nayak, glad it helped!

[@ITsEasyTraining1st]

You are my hero, I have been trying to get this to work for ages, yours is the easiest method

[@DougHExcel]

Glad you liked it!

[@willm7994]

This is awesome ❤

[@DougHExcel]

Hi Will M, thanks for the comment!

[@lordbuller1019]

really helpful, thank you

[@DougHExcel]

You're Welcome!

[@Sayyed33]

Nice Video Doug H Bro....... Thanks

[@DougHExcel]

+Sayyed Waseem you're welcome!

[@vijayvaladi]

This really solved my problem - Very good illustration

[@DougHExcel]

Thanks Vijay Kumar, glad it helped!

[@mrashad1988]

Thank you from Egypt :D

[@DougHExcel]

You're Welcome!

[@eshwarchintala1596]

Tanq bro...very use full information

[@DougHExcel]

You're Welcome!

[@Pankaj-Verma-]

Great Video.

[@DougHExcel]

Hi Pankaj Verma, thanks for the comment!

[@Meolimo]

Thanks Doug very interesting

[@DougHExcel]

Hi Mario Limoges, glad you liked it, thanks for commenting!

[@xosar28]

THANK YOU! 🙏🏼

[@DougHExcel]

You’re welcome!

[@wasimraja7449]

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]

Hi wasim raja, glad you liked it, thanks for commenting!

[@chinthakaherath3191]

Thank you!

[@DougHExcel]

You’re welcome

[@souvikdas59]

Thank you Boss...

[@DougHExcel]

You're Welcome!

[@karljolivet5991]

Great trick thank you

[@DougHExcel]

You're Welcome!

[@bohemaxxtum]

it's very usefull trick.thanks.

[@DougHExcel]

Hi bohemaxxtum...you're welcome, glad you liked!

[@halamangpinoy6113]

Thank you so much Bro :)

[@DougHExcel]

You're Welcome!

[@MrAnasmustari]

Tx Very much, its realy help me so much

[@DougHExcel]

Thanks Anas Mustari, glad it helped!

[@dritannaum4360]

Great solution, :)

[@DougHExcel]

Hi Dritan Naum, glad you liked it, thanks for commenting!

[@AndereXBuck]

Thanks!

[@DougHExcel]

You're Welcome!

[@danielrabello4837]

Thanks for share.

[@DougHExcel]

You're Welcome!

[@maddy3826]

Thanks for the video. It helped me alot. But the value did not change over "H2". How can I fix it?

[@TenorPitchKaraoke]

thanks this really help m

[@DougHExcel]

You're Welcome!

[@jeyakumarvj]

THANK U SO MUCH BRO👍😇🤝

[@DougHExcel]

You're Welcome!

[@VS-rh8rq]

great stuff..

[@DougHExcel]

Hi Vimal Saxena, glad you liked it, thanks for commenting!

[@muzicmaster8]

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]

Maybe this will help ruclips.net/video/srpqosbYhuc/видео.html

[@backendadmin1217]

Salute!!!

[@DougHExcel]

Hi Backend Admin, thanks for the comment!

[@mallesh.k3071]

I have article wise pull back details if i sacnning in excel sheet vlookup need find find how many left from article list

[@DougHExcel]

Sorry, don’t understand the comment/ question...

[@asrarakashah1184]

what does the quotation mark " " in "A18 used for ?
I am still a little bit clueless in this. thanks

[@DougHExcel]

when you concatenate a string/text like the colon, it needs to be enclosed in double quotes

[@mohammadyasin1105]

You are great

[@DougHExcel]

Hi Income Tax, thanks for the comment!

[@HudMan4U]

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]

Hi That Guy, thanks for the comment!

[@miaquaintrelle]

This is brilliant! Save me from wrapping my head around the 'Small' formula 🤭👍

[@DougHExcel]

So glad!

[@TheDulithaabn]

Thank you..

[@DougHExcel]

You're Welcome!

[@normundsbroks4595]

Thanks for this lesson! What if there is more columns and I want data from complete raw with different values to match?

[@DougHExcel]

Hi Normunds Broks, thanks for the comment! You may want to consider Power Query for lookup too ruclips.net/user/dough517search?query=query+lookup

[@normundsbroks4595]

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

[@versusstats7051]

Brilliant

[@DougHExcel]

Thanks for the comment!

[@BHARATSINARE]

THANKS

[@DougHExcel]

You're welcome!

[@anniekostadinova430]

Hi, can you show how to but from a different sheet? My master list is on a separate sheet and I tried connecting the with the helper column but it doesn't work. Thank you

[@DougHExcel]

When you use the mouse to click through the ranges on the other sheet to fill in the argument value it should take the sheet name with the cell (range).

[@terryfoster4104]

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]

This is like a lookup with multiple criteria. See these for some insights =>
ruclips.net/video/9APtjXEZzvs/видео.html
ruclips.net/video/_MP0jDs8XtU/видео.html
ruclips.net/video/ZFUcLRnlByY/видео.html

[@Tissue666]

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]

See if any of these fit ruclips.net/user/dough517search?query=power+query+search

[@pastelari]

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]

Try using either adv. filter ruclips.net/video/CQ5gnNG0teQ/видео.html
or
auto filter ruclips.net/video/XD0MHorKuUU/видео.html

[@terryfoster4104]

Hi, this works great. How would I show data based on two drop down boxes

[@terryfoster4104]

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]

maybe one of these can help ruclips.net/user/DougHExcelsearch?query=multi%20lookup

[@MariaBML7]

Thank you! But what if I want to get the last value in a range of unknown size? (constantly new values are being added to the column in which I'm having the results)

[@DougHExcel]

Power Query will provide all matches so this might help
ruclips.net/video/dxdt25BT5mA/видео.html

[@DougHExcel]

or this one ruclips.net/video/srpqosbYhuc/видео.html

[@manuelmatarueda897]

Thanks, simple and easy. Just remove the #N/A with its proper function and it will keep the spreadsheet totally clean.

[@DougHExcel]

Hi Manuel Mata Rueda, thanks for the comment!