Fax Smart Dropdown with airSlate SignNow

Location dropdown record

Can Excel have a searchable data validation list? This is one of the common questions I get on this channel. So here I have a data validation list. When I click on it, I get the list of all customers. To find one, I have to scroll and find the name. Now here comes the part of it being searchable. If I type in GAR and I click on this, I get the list of names that only include GAR. And notice that it doesn't have to be at the beginning of the name, it can be anywhere in the name. If I type in Rob and click on the down arrow, I get a list of names that include the word: Rob. And if I remove that, click on this, I go back to the full list. Now, if you look for this online, you're going to find a few VBA solutions. I also cover a VBA version which uses user forms inside my VBA course. But how about doing this without VBA? Until now, you needed complex formulas and complex data preparation to set it up. You pretty much had to be a very advanced Excel user. But not any more. (uptempo music) Let me show you how you can easily set it up with dynamic arrays. But please note that dynamic arrays are available in Office 365 for now in the Insider Edition, but soon for everyone using Office 365. Let's get to it. So this is where I wanna have my searchable dropdown list. Once the user searches for the customer, they select a customer name, they automatically get the company the customer works for. The list for my master data is inside the master data tab. We can see customer and company here. This list is not an official Excel table yet, but I'm gonna turn it into an official Excel table towards the end, so that every time we add in new customers our searchable data validation list is gonna include that customer name as well without us having to do anything. Ultimately, what I want to do is to have my list here, but somehow I need to create a separate list somewhere that restricts the names to the names that include the words I type in here. So when I type in GAR, I need somewhere a list generated of names that only include GAR. Now currently, dynamic arrays works well with names. So names in name manager, but data validation lists don't work with names that have dynamic array formulas in them, right, so I have to create a data preparation table for that, and I'm gonna do it right here. So any time the user types in GAR in here, I want my data preparation table to give me a list of customers that include GAR. One formula that helps us identify which names have a GAR included in them is the SEARCH formula. So let me just demonstrate this....