How to Create a Dynamic Searchable Drop Down List in Excel

How do I create a multiple searchable dynamic drop down list in excel?
There are three parts here. Data validation drop down list should work on multiple cells. It should be searchable. It should be Dynamic. In this tutorial I’ll show you how you can do all these at once. Most importantly I’ll be using different formula and functions only. No VBA required. You can use this to any excel versions from Excel 2010 to Office 365. There are a few things I’ve used in this tutorial. If you want you can learn them before starting.
I’ve also Used different for and they are as follows:
IsNumber Function: Video will be uploaded soon
Offset Function: Video Will be Uploaded Soon
Rows Function: Video Will be Uploaded Soon
Now that you learned how to use these functions and technique let’s follow the procedure to create searchable dynamic dropdown list that can be used on multiple places.
First create a table with your data. First column will be helper column and the second column will be the data you need to include inside your dropdown list. You can do this without tables but then your dropdown list will not work properly if you add new value. In this way i was able to create a 100% Dynamic, searchable drop-down list in excel.
Now you need to place the formula inside the first column. The formula I used in the video is as follows:
Here @Names indicates each cell of the name’s column from the table column. This formula will create a unique serial number each time if matches the value from Cell F1. Change the cells according to your need.
Now we need to extract the list based on our search. As our helper column is already sorting the values that matches the search, we need to just take it from there and fill it in a new column. In my case I’ve used column H. The formula I used here is as follows:
=IFERROR (VLOOKUP (ROWS ($H$2: $H2), Name_List,2, FALSE),"")
That will extract the value perfectly. Now our search feature is ready. Now I need to create a dynamic named range here and feed this search result inside data validation drop down list. Click on the formula bar. Click on the name manager and click new. Give a name for your dynamic list. And place the below formula inside the reference.
=OFFSET (Backend! $H$2,,,COUNTIF(Backend! $H$2: $H$500,”?*"))
Now the last part. Write the Cell Function in Cell F1
=Cell (“Contents”)
Now create your dynamic dropdown list and from the error alert tab uncheck the option “Show error alert after invalid data is entered”.
Done. You’ve just created multiple searchable dynamic dropdown list in excel using formula.
#DropDownList #Searchable #Dynamic

Thanks for watching.
-------------------------------------------------------------------------------------------------------------
Support the channel with as low as $5
-------------------------------------------------------------------------------------------------------------
Please subscribe to #excel10tutorial

Here goes the most recent video of the channel:

Playlists:

Social media:

[varadarajan_r]

You sir, are a GENIUS! Best solution yet for those without access to Dynamic Arrays in Excel 365. That too you have implemented it without any VBA/Macro or other such advanced functions that are difficult for non-experts. Beautifully explained and I just had to follow you step by step to get the outcome.

Thank you!

[FrankStock]

Great video. I had tried several different methods with no luck. This solved my problem. Thanks for sharing!

[sharknetsupport]

Love You Dude.. Such a useful Tip. I was searching this for 3 days...

[dotdotdotdot]

A very useful video, thanks Kazi.While I was testing it I wasn't sure about the user experience of trying to use the lookup list without entering anything in the search box.
ie if they just click on the scroll button on the search field then there is nothing in the list.
So I modified my version of this formula

=IFERROR(VLOOKUP(ROWS($J$4:J4), CountryOfOrigin, 2, FALSE), "")

I changed it to this

=IFERROR(IF(MAX(CountryOfOrigin[Helper])=0, INDEX(CountryOfOrigin[Country], ROWS($J$4:J4)), VLOOKUP(ROWS($J$4:J4), CountryOfOrigin, 2, FALSE)), "")

So basically if there isn't a value in the helper column then it displays the full original list.

Cheers, Richard

[LinuxSaravanan]

Good day, sir.
followed your instructions and worked well.
Thank you, sir; you are a genius!

[anhkiettran]

Thank you so much for this video
It helps me a lot

[muhammadakterhossain]

Thank you so much for such a wonderful video.

[sovithonhin]

Thank you for the videos. You deserve a big load of respect.

[emg]

thank you for sharing this video very infromative and it ie easy to follow how to do dynamic searcheable dropdown list especially i am using excel with no filter in formula box.

[MyChanNiel]

Thank you so much! you are a life saver. Been looking for this for a very long time.

[jatinsinroja]

So much helpful, I have been searching for this my whole day, thank you

[onlineservices]

Hi sir was very very useful. Very informative. Thanks. It had the features of searchable, dynamic and multi .in others it was not multiple rows .Thanks

[rockyrock]

This the close to solution what i got. Thank you. Is there a way to list all names before typeing?

[achellali]

very good, i was looking for writing my password DB, then have a search on a criteria like account name (unique) and then extract all relevant infor around this search. Worked well.

[fredericandre]

Wonderful application and great tutorial.

[LittleLord]

Really thank you for your effort we really appreciate it. I subscribed to your channel

[abdulrohman]

broo.. u are awesomeeee thanks a lot..

[ravishankaraprabhu]

Thank you very much. Very nicely explained. Can the list be sorted in alphabetical order? We have to give a space and then only all the items get displayed. While pressing the drop down without typing, no items are displayed. Is there a way to get over this.

[iffatsyed]

Sir as you are using office 360 you dont take to use search formula. But can you tell for excel 21 how I can add search formula to the formula in the video. I need searchable, dependable and dynamic dropdown list for my balance sheet. Thank you

[arlinecoetzee]

Great video! Could you kindly please let me know what your circular formulae settings are? (Max iterations and max change). Thank you!