Many SEARCHABLE Drop-Down Lists in Excel (No VBA)

Enhance your Excel sheets with searchable drop-down lists, ideal for situations where you need dynamic and interactive data entry. This tutorial is especially useful for creating multiple drop-down lists on the same sheet, tailored to professionals who manage large data sets or require efficient data entry methods.

🌟 Key Learning Points:
- Searchable Drop-Down Basics: Learn the fundamentals of creating a searchable drop-down list using Excel's dynamic array formulas, a method suitable for single-cell applications.
- Replicating Lists for Multiple Rows: Discover techniques to extend your searchable drop-down lists across multiple rows, ensuring functionality throughout a larger section of your spreadsheet.
- Data Preparation Table: Understand the importance of creating a data preparation table, which involves adjusting your original data set to suit the needs of the drop-down list.
- Using Dynamic Array Formulas: Explore how dynamic array formulas like FILTER, SORT, and TRANSPOSE can be used to craft the necessary list of options for your drop-down.
- Applying Data Validation: Gain insights into applying data validation to multiple cells, allowing each cell to have its own individual searchable drop-down list.
- Bonus Formula for Related Data: Learn an additional formula using XLOOKUP to display related data (like company names) once a selection is made from the drop-down list.

🚀 Practical Applications:
- Create efficient, user-friendly data entry points in your Excel sheets.
- Implement searchable drop-down lists for tasks like inventory management, survey responses, or any scenario requiring a selection from a large data set.
- Enhance your Excel reports or databases with dynamic, interactive elements.

00:00 How to create multiple searchable drop-down lists in Excel
02:00 Combine with legacy Excel formulas like the SEARCH function
08:30 Transpose functions
10:40 Bonus XLOOKUP function

🚩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

[liquidbydesign]

Thanks for your video!
I found a way to use multiple searchable drop-down lists with only one data validation prep list, that should reduce the file size if you have many drop-down lists.
If you input the formula =CELL("contents") into a cell, that cell is updated with the most recently updated cell value in the worksheet.
All I did was following the guide, using the formula =IF(CELL("content")=0, "", CELL("content")) in cell D1 and instead of Report!B5 in cell D2 I used D1.
With that IF-function included, the formula returns "" instead of 0 if the last updated cell is empty. An important change to be able to browse the complete list with no text entered.

[didasssendagi]

Leila, Thank you for the videos. Having watched your videos for the last three years has improved my excel skills. I appreciate your work. You are indeed a true practicing economist, who define public goods as non-rivalry and excludable. You sharing your knowledge with us doesn't reduce your content level.

[taypaddy]

Miss Leila, you're just making our jobs so easy. I ca'n't thank you enough,

[garys]

I really like the way that you explain each step and working the formula from the inside out. It helps me to understand what is really going on.

[warrenanderson]

Once Again... You answered my pending question when I saw the first Searchable Drop Down List you posted. You Are The Best!!!

[kamilkuczma]

Just yesterday evening I tried to figured out how to create multiple-row searchable drop-downs using dynamic arrays. Finally i did it hard way, by using indirect address, multiple columns and manually setting up all data validation for every cell in the range (Was nightmare:P) And today's morning i just found this
I feel so stupid now:) Great guide, just like always!

[chopps]

I actually did this today before seeing this video and came up with the same solution except I made it via Columns as opposed to transposed. That’s a great solution. Something I noticed was that the size of the spreadsheet was huge and a way around this was to set the spilled range to NULL (using an IF statement) unless the corresponding validation list had an entry in it. The difference in size was staggering.
I love your videos. Thank you

[doctoratomic]

I've only recently discovered your channel. Your Searchable Drop Down List video showed up in my recommended list and answered my long-time question if there was a solution without needing to know VBA. At the end of watching, I was asking myself, "But what about creating multiple searchable drop down lists?" Lo and Behold, the YouTube algorithm served up the answer with your follow up video. You are a great instructor, and I look forward to checking out your other videos. You have a new subscriber!

[AhmedMIbrahim]

Leila, you are a magician. I’m in love with Excel for more than 20years but you never failed to impressed me. I’m learning something new every time i watch your great videos. More importantly, while watching i’m smiling from start to end. It is really very entertaining 😂.
I wish i can meet you in person one day.

[debjitpaul]

This is a brilliant video tutorial for beginners like me. Thanks! But as i searched more, i noted that a much simpler way to achieve this from your last video would be to use the concept of "active cell" (CELL("content") formula) in your search formula and write one line of code in vba (application.calculate). I know this says "no vba" tutorial but since its that small a piece of code, you can mention that in such videos so users can choose wisely.

[mcd]

That is a complicated solution that requires a formula with a number of functions. Thank God you are here to hold us by the hand through the entire process.

Love your pronunciation of the word "character". ;-)

[aig]

What can I say that has not been said already you opened my eyes to so many options, explaining with so much clarity!! and solutions thanks a million

[jasontan]

My data entry form is an Excel table, hence to cater for dynamic data range. I'd suggest to use the INDIRECT(CELL("address")) feature within the SEARCH formula. This eliminate the needs to Transpose data and no need guess total rows needed.

[sharonallende]

Leila, THANK YOU!! You have solved my problems! Love you videos, You process is so easy to follow and understand.

[jkeithgreen]

I have been searching for a resolution to this specific drop down list scenario for a long time. Your videos are great and you do an excellent job explaining and walking through the steps in setting things up. You have always been and remain my "go to" when I have excel questions.Thank you so much.

[jasvirsingh]

Hi Amazing tutorial... thanx for all the wonderful knowledge that I have gained from the reference report!b5 is replaced with function Cell("contents")....this will get value to be searched from the active cell. Now now the dropdown can be copied other need to transpose and other steps....

[dreudax]

I just recently found out about this channel, its excellent, and you are an excellent teacher, it would have helped me a lot if I found it sooner. Your videos are extremely clear, and your delivery method, and production is top notch. Infact I can comfortably follow your videos at 3.3 times with perfectly legible audio, while I cap at 2.5 for most other tutorials.
Thank You.

[teoxengineer]

EXCELent performance…. incredible results and miracle of Excel...Dynamic array functions are useful getting these results.
Thanks Leila and Microsoft Excel team 👍✌

[Manhe]

Hi

Been a big fan of your videos for a couple of months now.
This transpose solution is just genius. Have seen other videos but solutions were too complicated.
If you ever think about spreadsheet size, mine went from 160 kB to almost 300 kB with your approach, I solved this with an IFERROR wrapping all the transpose function.
Only drawback I see is that the drop down doesn't feature a single name unless you type a single letter, it is a minor one for me.

[iscbala]

Thank you Leila for your immediate response. Actually I am having the monthly subscription pack for Office 365 Personal version. Kindly clarify