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. Instead of switching back
and forth from the report tab to the master data tab,
I'm just gonna act like my input field is right here,
so I'm gonna be typing in GAR and right here, I'm gonna
see a list of customers that include GAR, but let's
see how this search function can help us with that. Search needs these arguments. First argument is what
text we're looking for, so that's what we're gonna be typing in. Where are we looking it up? We're going to be looking it up here. Actually, it's gonna be the full list, but let me just show you
what SEARCH actually returns. The last argument is optional,
it's where do we want it to start looking, like which position. Well, in this case, we'll
always want it to start to look for that name from the beginning. Right from position one,
so I can leave that empty. So what do I get here? I get a number back, and
the number is the position of this name in here. So if I switch this to M, what
do you think I'm gonna get? Six; because M is the sixth
position in this name. And if I switch this to a
letter that's not in the name, I get an error, right? So basically, I get either
a number, if it's in there, or an error if it's not in there. Now let me just apply this
to the full range here, and this is where we can see the advantage of a new dynamic-array-aware Excel. So instead of looking for this in A2, I'm going to look for
it in the entire range, so control-shift down, and
then I'm going to press enter. And my formula spills. So it gives me all errors,
because all of these don't have V in there;
this one does, and that's in the fourth position,
and the rest don't either. So if I switch this to Rob
now, we can see numbers and errors, so what I wanna
do is to convert these numbers and errors to true and falses. So if it has a number,
it should be a true, and if it doesn't have a
number, it should be a false. So I can use the IsNumber function here. And just wrap this up in there, press enter, now I get my
false and true values in here. So now we can move on to the next function that can filter this list and
just give us the TRUEs back. And a great formula for
that is a new dynamic array formula called the FILTER function. We need to define the array
that we want filtered, so basically, what do we
want to see back here? We want to see a list of customer names, so control-shift down to
select the whole range. The next argument is:
what do we want included? And we already have our
true and false values here. So filter is just gonna include
the true values in here. And the last argument is:
what do we want it to show if it's empty, so if it
doesn't find anything? I'm just gonna put: not found. Okay, so when we type in
Rob, we get the list of names that include the word Rob. If I type in V, I just get one, and if I type in something
that's not there, I get: not found. Okay, so that works well. It's just that we're not
gonna be typing here. I'm actually just gonna call this: data validation prep. But instead, I'm going
to change that reference to search in here (Report tab). So in that cell, I had GAR, and I get my data validation
preparation list, showing me the names that include GAR. Notice that my customer
list here is a list of unique values. If yours isn't unique, so
if you have Robert Spear mentioned a few times, you
wanna get the unique list back, all you have to do is wrap this up, inside the unique function. But in my case, I don't need to do it, because I have a list
of unique values here. But also if I wanted this to
be sorted, I can also wrap this up inside the sort function. Okay, so actually, let's do that. Now as a next step, all
I have to do is to get my drop down in here, so
I'm gonna go to data, data validation, under settings, I'm gonna select list, for source, we're gonna go to master data, we're gonna click on the first cell that has our main formula, and we want our entire spill array, so the entire spill range, I
need to put a hashtag (#) in there and I say: okay. So when I click this,
I get the list of names that include GAR. Now, there is one setting
we need to change, because if I type in something
new and I click this, I get an error telling me
this value doesn't match the data validation restrictions
defined for this cell, right, because it doesn't
find Rob in that list. So I'm gonna click on cancel
and go back to data validation. Under error alert, I need
to take away the check mark. Show error alert after
invalid data is entered. And I say: okay. So now if I type in Rob
and I click on this, there's no alert. My data preparation list updates. So does my data validation list. Now the aim is also to get
the company that this person works for; now here you
can use VLOOKUP, you can use INDEX and MATCH, but since FILTER is one of my favorite formulas right now, I'm gonna use that. So our array is company, right, because that's what we want to get back. What we want included is
the customer that equals the customer that we select right here, and as the last argument, if it's empty, I'm just gonna put: nothing (""). Close and enter. Now if I'm just typing
something in like M, and I click here, notice that
it doesn't return anything. Right, because I said
if it doesn't find it, it should show nothing. And then once I click something,
then it shows the company. Now if I look for something
that's not on the list, I also get nothing, and
when I click on this, it just says, okay, Leila's not found. Now, let's go to the part where
we can add in new customers and get our list to automatically update, without us having to do anything. So we're going to take advantage of Excel table functionality. So all I have to do is turn this data set into an official Excel
table, so you just click anywhere inside and press control-T. My table does have headers. Just click on okay, and just
gonna go and clear the design. And call this table: customer. And press enter. I don't really have to
do anything anymore. All I have to do is just
go and add in my names. And let's go to my report, type in Leila, and click on this and I get Leila Gharani. And this formula updates
automatically as well, because it understands
that the source data has changed into an official table, so it expands the formula range as well. Now I know it can be
frustrating if you don't have dynamic arrays, but if you
have Office 365, it's coming. And if you're excited
to get dynamic arrays, click that like button. Once they're there, you're gonna
be one of the first to know how to create searchable
dropdown lists, without VBA. I'll keep you updated, so
if you're not subscribed to this channel, consider subscribing. (uptempo music)
