Create a searchable drop down list in Excel

[@markhuang368]

I can not believe that this is a video posted 11 years ago when there was no spill array function(like Filter function). You are a master.

[@LadyKhione]

Just wanted to post a comment so you know that your video is still useful today! currently useing this formula as a member of my HR department and its been super useful to those of us still working without some of the newer features not available for Microsoft 2016 - thank you!

[@Andosier]

Hi Neil, Thanks for posting this great tutorial! Now in 2020 most tutorials focus on the FILTER function, which is exclusive to the Excel365 edition. Very thankful that you posted this about dynamic ranges for all of us who have older or stand-alone versions.

[@Cthulch]

I just want to express my gratitude. The technique described is quite elegant. Good job.

[@voldemort786]

Thanks! 9 years later and I actually needed this today lol that's a million

[@mdcs1992]

I know this has been up a long time but I happened to Google the exact name of the video. It's complicated but extremely well explained and when I not only got it to work but got it to work in different cells with different validation..well..satisfying. I started out with Excel in about 1998. Back then you had to be able to work around stuff in ways that these days are just automated by a GUI. Anyway, thanks all these years later for uploading this.

[@DrawWwithMeEehHH]

WOW!! NOW THIS IS A LEGENDARY FORMULA TRICK! IMAGINE UPLOADED IT 11 YEARS AGO; BUT NOT LATER 11 YEARS AGO, EXCEL PROVIDED A SIMPLE "FILTER" FORMULA AND YET NOT ALL EXCEL IS APPLICABLE ON THAT. NOW SIR YOU ARE A TRUE FATHER OF EXCEL. THANK YOU!!!!

[@mmyers821]

This is one of my most favorite Excel tools learned from KZbin. It's presented very well, extremely well explained step-by-step, easy to follow and replicate. I put together a series of searchable drop down lists in tables, one for each row of a quote document. Now the user can search a short list of parts to select for the quote. This is much, much easier.

[@elbadlis]

Watched this when i started learn Excel. Today i watch again and i feel grateful because this video actually taught me a lot about Excel formula.

[@angelise9467]

Sir, I have watched lots of excel tutorials but for me, I have learned more from you. From your videos I have maximized more functions. I also learned how to extract records from multiple categories simply by replacing the search function to and function. Thank You so much!!!

[@edkeith]

Brilliant! Clear and concise walkthrough with just the right amount of details. Thank you!

[@johnmartin1645]

Great explanation. You build incrementally, one concept at a time which make it understandable. I just had to watch it more than once to understand it all. Thanks for this.

[@bednarczyk]

Wow, that was such a great and simple tutorial. I especially LOVED how you explained the formulas step by step instead of just saying "type this long formula into I2 and you're done" like many websites and videos say. I actually understand what is going on with that formula!

[@abdulsalam935]

Sir, VERY SLOW & CLEANLY explanation. Previously I used to follow the procedure blindly watching other videos. I am now very confident to write the formula after watching your video. Thank you very much Sir.

[@kevorkvartabedian8209]

even the Video is old you deserve more than 10000 likes. thanks for the great explanation .you saved me lot of time.

[@kevorkvartabedian8209]

plus you deserve sub

[@nickt9857]

Back in 2012 when this was uploaded, I bet not many people would have been able to guess who the future US President and UK Prime Minister would be in that sample list!

[@PedroDelfin]

Wonderful remark xD

[@sametakcan1291]

So, Angelina Jolie can be the next president?

[@kikin310]

who cares

[@nickt9857]

@@kikin310 You obviously care enough to leave a comment 🤣

[@billycash9905]

This what I am looking for in so many weeks. Dynamic drop-down list without macro or VBA. Thank you Neil.

[@cooleustary]

i'm totally an excel noob and I actually successfully manage to make the damn drop down list, thank you very much!

[@pattufreefincal]

Hi Neil, I was referred to this video by someone who used my financial calculators to improve the user interface. I did everything as you stated and it worked like a charm. Thank you very much.

[@kermitgarrett]

I am so much in tears right now! THANK YOU SO MUCH for sharing what you know with all of us! You helped me and all of the people who'll be using the template I'm preparing. Thanks again!

[@85haker]

Thank you so much for putting this together. This info, plus the additional info from part 2, will save me so much time. A quick side note, I started to experience excel not responding shortly after implementing this. But it was do to some of my other sells using index\match of an entire column. When you use reference an entire column ($A:$A) you are asking Excel to look at over a million rows. Keep this in mind if you run into excel not responding.

[@johnserdena5518]

Hi Neil, I am so grateful to see this video. This helps me bigtime with one of my job. Wonderful!

[@ytsedome]

This is some serious bad ass excel skills right there, people

[@movzx0fh]

A bit complex but truly sophisticated; congratulaitons and very many thank you's, Sir!

[@timntracywhite3653]

This was a great video. I have a very large database and trying to scroll through hundreds of products it to find a certain one was very time consuming. This video helped me to make my database a searchable one. Thank you.

[@PerezerepL]

Great learning experience. I was looking for something else, but found this and I was hooked. Thank you teacher.

[@zzztem]

Wow. It's 2020 and you are still helping folks out with this video. Thank you! Works great.

[@mimfriday]

Thank you! I have studied so many versions of instructions and this one worked!

[@baldvinjohnsen1665]

Fantastic solution to an annoying problem...thanks Neil for a great video tutorial and solution

[@SanjeevKumarZopfan]

Hundreds of people have tried to find a way to create such a drop down choose able list without venturing into VB. But no body could. I myself have googled much but couldn't find a way to do the same, until of course I came across this video of yours. Bravo buddy. You really are an expert. Now, one thing. Couldn't you provide the exercise file along with. So that the less smart people like us could follow along your guidelines while watching the video.?? Still, thanks buddy. You rock!!!

[@donfiander1534]

Works great with one position!! I just found your followup tutorial on adding the feature to multiple rows!! This just anwered my newest question today. Thanks for the great work!

[@oscargalvis7421]

GREAT VIDEO!! thanks it is really helpful!!! not just for the dynamic drop down list, but for how you conjugate formulas... it gives me a lot of ides... thanks so much!!

[@jerrythefisherman94]

Mr. Firth: I just finished studying and putting into action the videos on how to Create a Searchable Drop Down List in Excel (1&2). After several hours of going between your videos and my application, I got it to work. I am associated with the Saint Vincent DePaul Society, and part of what we do is deliver food and other items to a food pantry. We need to keep track of how much we take there. I use this Excel application to do that. Your videos were of great help to me. Thanks.

[@juansolana]

I dis this formula in the source of a drop-down list and it worked very well: =OFFSET(Sheet3!$A$2,$B$22,0,MATCH("*",Sheet3!$A$2:$A$45,-1)+1,1) Where $B$22 = MATCH(A22,Sheet3!$A$2:$A$45,1)-1) :)

[@Wasmir1954]

Thank you very much Neil. This method is very useful and I found the second part (as your response to those who wish to incorporate the function to other cells) more appropriate for my application. This is exactly what I was looking for. More power to you Neil and thank you so much. Mabuhay from the Philippines!

[@berke2921]

Neil, that is the best video I have ever come across! very useful, thanks for efforts .

[@MyHUPPA]

thanks a zillion trillion billion million ... u made me a genius in excel by the end of ur vid.tutorial

[@MsMadelineTwoPointOh]

Don't need this just this minute, but it's GREAT to know it's here when I do. Thank you. Good stuff.

[@danilyncallo6936]

Thank you very much. Your explanation is very easy to understand and can be use to other excel version.

[@tontonpabilonia]

This is exactly what im looking for! process is a bit complicated but does what I want to have on my spreadsheet! thank you so much Neil!

[@cathygraceful]

This is a fabulous workaround and easy to set up. Many Thanks

[@Xyour_starX]

Hi Neil . It comes from an Innovative and genuis mind. Thanks a lot.

[@dhdoom]

Thanks Neil, based on your 2 vids I have made myself a version for scientific names using LEFT(cellRef, LEN()) to restrict the search to as many letters of the genus name as the operator enters! Very handy for a list of 4000+ names.

[@dennisglover9836]

Many thanks for the clear explanations at each step. This has helped me greatly!

[@keithcaruana4285]

Life saver. Brilliant tutorial and very easy to follow.

[@vasu2k123]

YOU DON'T KNOW HOW MUCH HAPPY YOU MADE ME TODAY, I WAS UPSET WHEN FILTER FUNCTION NOT AVAILABLE IN MY EXCEL BUT YOUR WAY IS AN OUTSTANDING SOLUTION. THANKS A TON. BE BLISSFUL .

[@DeckaMac]

really great, i love the way you explain every part of each formula. people should teach like that more often.

[@Cadrieldur]

The simplicity of this is awesome! Just a powerful search button. Thank you

[@123DZDZ]

Very good job ! I wonder something, people who disliked the video, do they have a better one ?

[@peterinth]

I'm impressed. Well presented and all formulas stepped through. Thanks I learnt some very useful procedures.

[@kylebouley8415]

Do you have any input on the above comment from me?

[@chrisweeks2602]

super helpful. Nice and thorough detailing along the way.

[@TH15N4M315UN4V41L4BL]

Thank you very much for this. I have seen other far more complicated and excessive ways to do something similar, but this one is the most simple and logical. It's a great help. Cheers mate.

[@aeilkeham3243]

Excellent video. Thank you !! It is exactly what I was looking for since I do not have Dynamic Arrays.

[@PriceybutComfy]

Thanks Neil! This has given such a great solution to a big issue I was having. My little tweak is that I've given up v/hlookup as my 2016 new year's resolution and prefer INDEX or OFFSET (MATCH) as this can speed things up with a really big file.Your step by step approach was really perfect. Thanks again!

[@hassalmua1691]

Excellent stuff. Greetings from Papua New Guinea (North of Australia).

[@TiagoCruz-sl2kq]

Genius!! Very well explained, simple, and it works.

[@granand]

Genius 100% ..simple ..not for me ..I am dumb maybe 👍😉

[@mikerudzinski7551]

Great tip! In Excel 2010, if you protect this sheet with typical settings, you cannot click the drop down until you type and press enter in the cell. Make sure you choose the "Edit objects" setting when turning on protection for it to work as normal.

[@anilprabhu9137]

WOW

[@wha91590]

Brilliant Neil. I only got halfway and gave a thumbs up and subscribed. A good practical use of formulas that I have known of for many years. (Or should that be ''tears")

[@AftabAlam-jo8jn]

No idea how useful it is ... saved me

[@DonWolfi007]

Neil, thanks muuuch! this has been most helpful! simple and like the explanation of why you go to the end result

[@redwantareq]

WOW, it looks so easy after your presentation, i have trying this for last one year. GREAT!! Thank you!!

[@redwantareq]

@m p ha ha ha well said

[@claytonkebongo952]

Thank you very much Neil for this very productive piece.

[@ayafarid2729]

This has been really useful and it worked successfully

[@thefarmer4586]

Mate, you are a genius. God bless you.

[@carolyngordon80]

Exactly what I needed, thank you! Your explanations were very clear and understandable and made the process easy listening and learning.

[@orlasmonk4829]

This is what I would call an advanced user! you're the shit!

[@bobdezaaijer5848]

Thanks Neil, after long searching on the web I finally found the answer for my searching problem.

[@SyeedChowdhury]

Dude, you are a lifesaver!

[@purplepen5666]

Neil! You are an Excel Guru...

[@rajeshshah6758]

SIr, You are just simply great in teaching.

[@Robert8455]

I think I like this better than another way that uses VBA. Going to give this a test. Thank you

[@mik9221]

Very helpful indeed, it makes one understand how excel truly works, I have just learnt of the new excel function #filter and have noticed it does the trick up to leave us just before the offset function. you can also try this formula dear colleagues for those with Office 365 or Microsoft 365 { =FILTER(G2:G30,ISNUMBER(SEARCH($D$2,G2:B30)),"") } Microsoft is ever improving. But otherwise thanks so much for the great lesson.

[@danemmerich6775]

Neil....that is Awesome to have! You lost me so fast, but it is me. I am trying to jump in the deep end of the pool considering I am so into Excel now.

[@SonalShethDAYA]

Exactly what I wanted...very nicely explained...if possible write step by step formulas to be used in your description

[@cygneblanche]

amazing, thank you so useful after a day of searching for dynamic vlookup !!!!!

[@crc_12z28]

the best!!!! big time the best tutorial , Thank you very much

[@juanpablocontreras3416]

I found this video quite helpful. Keep it up. I love your channel.

[@alphajuliet444]

This is the most simple video i have even seen for the purpose. It would be great if you could also share the technique wherein the drop down shall reflects the suggestions based on the details entered in search box, without clicking on drop down arrow.

[@forrobertrichard]

Brilliant. Solves a problem that I am currently working on. Thank you!

[@MahmudYuldashev]

in this simple task I found out so much new for me. Thanks man a lot for this video

[@TheRaosalman]

kudos. May ALLAH give u reward in this world & hereafter

[@johnellington2782]

Brilliant I have been looking for this solution for ages.

[@susancordeiro5005]

Hi Neil! This was absolutely awesome! Fantastic job....

[@zakylib]

I loved this at the beginning then you lost me when more functions jumped in. But great description Neil. Now how do I use this in a table which will be populated with more items (names)?

[@sampathruwankumara9833]

Excellent......... It is very helpful to me.....Thank you very much....

[@nagarajujangala4185]

VERY USEFUL FORMULA AND VERY GOOD EXPLANATION SIR

[@umeshgawas]

Awesome Neil. Thanks for the video. I got exactly what i wanted.

[@kalpeshpatel1057]

That is the best implementation of features together !!

[@ndabawangure4322]

Many thanks Neil. Articulate. Step by step.

[@AZGATOR2002]

Very very good stuff. I have seen each of these separately but itis the combination that iis powerful!

[@gamerdweebentertainment1616]

Thanks, I will follow up on this later. I just need the basic one, don't have a huge list. here's a like.

[@shahebacharjee234]

U r best.....& also thanks for, the search down list in a multiple cell.!!!

[@jamespyle6398]

This was sooo convoluted..but it works lol =D (I don't mean your video and explanation was convoluted I just mean the whole process to do it, your video was great!)

[@jamestaposhadhikary7381]

Very helpful, thanks Neil! Just wondering, if I need to use such a 'Dynamic list' (the same list) to fill up every cell in a column of a table, how do I do that! Can you please help in this regard?

[@anneliennel4373]

Thank you very much, this was really helpful. How can you do it for more than one cell in the same sheet?

[@timkochanski]

Brilliant, for an entire column solution I used an INDIRECT. Instead of referencing your search cell ("D2" in the video) put the following formula in that cell and you can do your search in any other cells in the workbook: D2 = IF(CELL("contents")>0,INDIRECT(CELL("address")),"") So, in cell F2 in the video you would have =IF(ISNUMBER(SEARCH($D$2,K2)),MAX($F$1:F1)+1,0) I do have to double click on my drop-down for it to work (refresh the cell address) though.

[@niketshukla3813]

Hi Tim, I am trying to do the same for the entire column. Could you provide a bit more explanation on how you did this?

[@wilfredsithole1988]

@@niketshukla3813 in cell E2 enter the formula =INDIRECT("$D$"&COUNTA($D$1:$D$100)), Assuming $D$1:$D$100 is the range you will have with dropdowns. The COUNTA($D$1:$D$100) part of the formula returns the row number of the last cell in the specified range. So if you have typed in cell D7 (assuming there are no blank cells in the range), the formula will return $D$7 as a string. The indirect function converts the string "$D$7" to a formula which returns the value in cell D7. Then change SEARCH($D$2 to SEARCH($E$2

[@wilfredsithole1988]

@@niketshukla3813 instead of using counta to get row number, which doesn't work well when there are blank cells in your range, use =MAX (ROW (INDIRECT(CELL("address")))). Incorporating this will get you the active cell which will be your filter criteria

[@renjithr2834]

thank you so much

[@RatneshVermaRtn21]

Thank you Neil. I tried and it's working find. thanks!!!

[@roberthart398]

Thank you again Neil! Part 2 was exactly what I needed. Brilliant!!

[@ianahn7501]

Awesome. Thanks for the solution. Really appreciate it.

[@dwaynemilner1171]

HI Nile, This is a fantastic video saved me so much time. Thanks a lot