Create a searchable drop down list in Excel 

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.

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.

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

This is one of my most favorite Excel tools learned from RU-vid. 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.

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.

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

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!

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.

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!

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!

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.

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.

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

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

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

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!

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.

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.

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

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

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

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

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

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

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.

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

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

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.

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.

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

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

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

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

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

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!

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

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

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

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.

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

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!

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

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.

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

Life saver. Brilliant tutorial and very easy to follow.

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

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!

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!

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

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.

super helpful. Nice and thorough detailing along the way.

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

Mate, you are a genius. God bless you.

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

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

Neil! You are an Excel Guru...

Thank you very much Neil for this very productive piece.

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

SIr, You are just simply great in teaching.

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.

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

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

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

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

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

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

This has been really useful and it worked successfully

Dude, you are a lifesaver!

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

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 .

Hi Neil Thanks for this video, it works great on one of my workbooks. I do have a slight problem at the moment. You see, on one instance in this workbook it works just fine but in another it's giving me the "circular reference" error and I have followed your instructions to the T, except I'm using tables for data population. My formulas is a follows; sheet 1 cell A2 : =IF(ISNUMBER(SEARCH('Weld Calculation Report'!$C$5;[@[Steel classifcation]]));MAX($A$1;A1)+1;0) sheet 1 cell A3 : =IF(ISNUMBER(SEARCH('Weld Calculation Report'!$C$5;[@[Steel classifcation]]));MAX($A$1;A2)+1;0) This works great sheet 2 cell A2 : =IF(ISNUMBER(SEARCH('Weld Calculation Report'!$C$10;[@Electrode]));MAX($B$1;B2)+1;0) sheet 2 cell A3 : =IF(ISNUMBER(SEARCH('Weld Calculation Report'!$C$10;[@Electrode]));MAX($B$1;B3)+1;0) Cell B3 should give the value of 1 and B6 should give the value of 2 in this case, but when I auto-fill if freaks out with the circular reference and give me 0s all the way. I'm stuck. The semicolon work here as the comma does at your side. Any help will be appreciated.

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

Many thanks Neil. Articulate. Step by step.

VERY USEFUL FORMULA AND VERY GOOD EXPLANATION SIR

Hi Neil, Thank You For the Tutorial.. it's just Awsome... Ur teaching style is really appreciatable..

No idea how useful it is ... saved me

That is the best implementation of features together !!

Thanks for the great video. I implemented the method in my workbook and added an extra feature: I wanted to be able to reference the same dataset with data validation from different cells in different worksheets. Some googling pointed me to user-defined functions (UDF's). I added the counter column and dynamic list to the left of the complete list, with the counter formula: =IF(ISNUMBER(SEARCH(INDIRECT("'"&ActiveSheetName()&"'!"& ActiveCellAddress());D5));MAX(C$1:C4)+1;0) The UDF's are really simple and should be put in your VBA editor, Module1: Function ActiveSheetName() Application.Volatile ActiveSheetName = ActiveSheet.Name End Function Function ActiveCellAddress() Application.Volatile ActiveCellAddress = ActiveCell.Address End Function Now, the defined name used in the data validation should have a reference looking like this: =OFFSET(Malt!C$5;;;COUNTIF(Malt!$B$4:$B$103;"?*")) (Where, of course, Malt! should be replaced with the name of the sheet where you keep your list. Malt? Allright - so I'm a homebrewer ;-) For me, this method seems to work better than what is described part 2 of this video, posted Sep 9, 2013.

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.

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

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

This is absolutely fabulous, thank you for posting.

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

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

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

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.

Hello NEIL ;; i really wants to thank you for this video ;;; THANK YOU , THANK YOU ;;; THANK YOUUUUUUUUU

Saw it today and learnt a lot. Would add one more layer of sorting by alphabetical order so that the dynamic list is always sorted

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

Very talented. Thanks for sharing.

Exactly what I was looking for, just brilliant.

Greet , many thanks , explain every step very well

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

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

This tip is what I've been looking for. Thanks a lot. By the way, I have one question. In the beginning of your video, you do not have any value on column F, but New Drop Down List(D2) works. How does it work?

Brilliant I have been looking for this solution for ages.

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

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

So the trick is to have a data validation set up with a dynamic range and disable the error to allow activity within the dropdown list. This is great! Although i do think am improvement is to not use vlookup and just implement the search counter inside the countif freeing a column.

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