Searchable Drop Down List in Excel (Very Easy with FILTER Function)

Discover a straightforward method for creating searchable dropdown lists in Excel without using VBA. This tutorial is perfect for those looking to enhance their Excel skills and add dynamic, user-friendly elements to their spreadsheets.

This technique uses data validation and (simple) Excel formulas to create a searchable data validation drop-down list. We use the Excel SEARCH function, ISNUMBER as well as the brand NEW Dynamic Array FILTER function. To make sure our searchable data validation drop-down list is sorted, we use the new dynamic array SORT function.

In this video, you'll learn:
▪️ Setting Up Searchable Dropdowns: Step-by-step guidance on creating dynamic and searchable dropdown lists using Excel's powerful features.
▪️ Utilizing Excel's Dynamic Arrays: Learn how to leverage Excel's new functions to make your dropdown lists responsive and efficient.
▪️ Enhancing Data Validation: Tips on improving your data validation process for a smoother user experience.
▪️ Incorporating Excel Functions: Insight into using Excel functions like FILTER, SORT, and UNIQUE to create more functional dropdowns.

We also use the FILTER functions instead of VLOOKUP or INDEX & MATCH to find the value that matches our selection.

00:00 Searchable Data Validation in Excel without VBA
01:11 Searchable Drop-Down List with Excel Dynamic Arrays
02:53 Excel SEARCH function
04:56 Excel ISNUMBER function
05:18 Excel FILTER function
06:42 Excel UNIQUE function
07:05 Excel SORT function
07:16 Create Data validation list with Excel Spill Array
08:26 Excel FILTER function for Lookup
09:28 Using Official Excel Tables

LINKS to related videos:

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[karthikbhupathy]

Content - 10/10
Way of Explaining - 10/10
Your knowledge and teaching skills are extraordinary!!

[steves]

I used to do something similar with those awkward Combo Boxes.
Liela's method is superior and has the added plus of auto-expansion with additional rows of data.
Invested 11 minutes into the video, followed by 5 minutes applying my new found knowledge and 16 minutes later, my colleagues were ready to run with a new and much improved model. 🙌
Thank you!!! 👍

[stuartkay]

Excellent!

First I've been looking for a way to allow users of my sheets to narrow the DV list based on letter searches, without VBA. So thank you.

Secondly, I didn't know about the new 'Sort', 'Filter' and 'Unique' functions. These will save me a lot of time using permutations of INDEX MATCH COUNTIF etc.

You've added value to my work, thank you!

[woutert]

This looks awesome. I've made a searchable dropdown before but it was way complicated and required like 4 extra column before it would work. Lots of data that had to be hidden on the same worksheet and that could be messed up by inexperienced users. Dynamic Arrays and the Filter function sound like the late Christmas present we didn't deserve but desperately need. Great video!

[DebashisTalukdar]

Leila,
Thank you so much for sharing so many of these Excel tips and tricks. I'm an advanced Excel user with over 20 years of Excel Development behind me, and I find that a lot of these new features that you share are immensely useful! Thanks so much for putting these videos out there in an easy to follow and understand format! You are AWESOME!

[shawnw.]

Thank you so much for this video!! I own an auto mechanic shop where each tool is entered to inventory by the 'office' people but the mechanics need to quickly search if we have a tool and where it is located. I entered the formulas into my spreadsheet in one window, right along with this video in another window, and published it to the shop's desktop at the end of the video. 11 minutes invested watching this video will save my staff hours of having to search the tool room to determine if we have a specific tool and if yes, where it is.

[AlexandreGentilSousa]

Definetely, Leila is the best Excel teacher ever ! I´ve been browsing thought many channels looking for soluttions to a new Excel system I have developed to a small business and most of the time is Leila that has saved me. All explanations are very
didactic, she is very sweet and we always get other knowledges together. Many, many, many thanks !

[abrotherinchrist]

Best Excel channel out there. I like that you are always on the cutting edge of new features. I've been using Gsheets lately as well and I really like that you can select entire ranges from one cell onward like this: A2:A (which selects A2, A3, A4...). I wish Excel had that without a lot of complication.

[HectorMtnz]

Totally mind-blowing that this material is free of charge. Great content, keep it up! :)

[simm]

Excellent illustration dear Leila Gharani !
Effective useful for the IT development for the human society !
Appreciate your great services in these training videos !
We wish you and your family / team all the best of time, health and peaceful happiness !

[saschakarbacher]

Hi Leila. Thank you so much for your videos!
I‘ve started working on a little company and they have NO tools for analysing their business. So I have to create Tools for Monitoring, vacation planning and much more. With your Videos, I can create and finalise my Ideas. Thank you for your overall good work!

[Avi]

You are brilliant, even after years of using Excel I always learn a few things from your videos.

[alikadim]

I had watched office tutorials since years, and without any doubt you are the best

[boele]

I see a huge value in this!! Big fan of your videos Leila! You have no idea how much you have influenced the way I am using excel nowadays. Big promotor of your videos. Often promoting them to my team all over the world. Thank you so much!

[longbill]

Thank you so much! beautiful Leila. I am using excel a lot, but still learn new thing from you. Love your voice and your way of teaching. You rock!!!

[nmbspltd]

This channel is superb. Would recommend it to all excel users.

[yusof-denjamasali]

I really like your videos. The pace can easily be followed, the video editing is excellent, your voice is so calming and you are beautiful. Thumps up. Thank you for sharing your knowledge, ma'am. God bless you.

[maheshpatil]

I have referred lots of youtube channel for excel but you are cut above all channel. you are leading excel teacher on youtube.

[ajitjoshi]

I am very thrilled by the searchable drop down list. I tried using the method and It worked. I also saw the link in the comment below to extrapolate the list to other rows. Thanks a lot..

[blue-oceandreamer]

This 2019 video production shows what a Excel master Leila has been while compared with another "cookie" video produced by someone else 2 years later. To surpass Leila's originality, creativity and well thought layout and illustration has never been an easy task.
Leila, I love your tutorial and teaching talent so much.
Time's well spent. Thank you.:)