馃攳
Searchable Drop Down List in Excel (Very Easy with FILTER Function) - YouTube
Channel: Leila Gharani
[0]
Can Excel have a searchable
data validation list?
[4]
This is one of the common
questions I get on this channel.
[7]
So here I have a data validation list.
[9]
When I click on it, I get
the list of all customers.
[14]
To find one, I have to
scroll and find the name.
[17]
Now here comes the part
of it being searchable.
[20]
If I type in GAR and I click on this,
[23]
I get the list of names
that only include GAR.
[27]
And notice that it doesn't
have to be at the beginning
[29]
of the name, it can be
anywhere in the name.
[32]
If I type in Rob and
click on the down arrow,
[35]
I get a list of names that
include the word: Rob.
[38]
And if I remove that, click on this,
[41]
I go back to the full list.
[42]
Now, if you look for this online,
[45]
you're going to find a few VBA solutions.
[48]
I also cover a VBA version
which uses user forms
[51]
inside my VBA course.
[53]
But how about doing this without VBA?
[57]
Until now, you needed complex formulas
[60]
and complex data
preparation to set it up.
[63]
You pretty much had to be
a very advanced Excel user.
[66]
But not any more.
[68]
(uptempo music)
[71]
Let me show you how you can easily
[73]
set it up with dynamic arrays.
[76]
But please note that dynamic
arrays are available
[79]
in Office 365 for now
in the Insider Edition,
[82]
but soon for everyone using Office 365.
[86]
Let's get to it.
[87]
So this is where I wanna have
my searchable dropdown list.
[90]
Once the user searches for the customer,
[92]
they select a customer
name, they automatically
[95]
get the company the customer works for.
[97]
The list for my master data is
inside the master data tab.
[101]
We can see customer and company here.
[103]
This list is not an
official Excel table yet,
[107]
but I'm gonna turn it into
an official Excel table
[110]
towards the end, so that every
time we add in new customers
[114]
our searchable data validation
list is gonna include
[117]
that customer name as well
without us having to do anything.
[121]
Ultimately, what I want to
do is to have my list here,
[125]
but somehow I need to create
a separate list somewhere
[129]
that restricts the names
to the names that include
[133]
the words I type in here.
[135]
So when I type in GAR, I need
somewhere a list generated
[140]
of names that only include GAR.
[143]
Now currently, dynamic arrays
works well with names.
[148]
So names in name manager,
but data validation lists
[152]
don't work with names that
have dynamic array formulas
[156]
in them, right, so I have to
create a data preparation
[160]
table for that, and I'm
gonna do it right here.
[163]
So any time the user types in GAR in here,
[166]
I want my data preparation
table to give me a list
[170]
of customers that include GAR.
[173]
One formula that helps
us identify which names
[177]
have a GAR included in
them is the SEARCH formula.
[182]
So let me just demonstrate this.
[184]
Instead of switching back
and forth from the report tab
[186]
to the master data tab,
I'm just gonna act like
[189]
my input field is right here,
so I'm gonna be typing in GAR
[192]
and right here, I'm gonna
see a list of customers
[195]
that include GAR, but let's
see how this search function
[198]
can help us with that.
[201]
Search needs these arguments.
[203]
First argument is what
text we're looking for,
[205]
so that's what we're gonna be typing in.
[208]
Where are we looking it up?
[209]
We're going to be looking it up here.
[211]
Actually, it's gonna be the full list,
[213]
but let me just show you
what SEARCH actually returns.
[216]
The last argument is optional,
it's where do we want it
[219]
to start looking, like which position.
[221]
Well, in this case, we'll
always want it to start
[224]
to look for that name from the beginning.
[226]
Right from position one,
so I can leave that empty.
[230]
So what do I get here?
[233]
I get a number back, and
the number is the position
[236]
of this name in here.
[238]
So if I switch this to M, what
do you think I'm gonna get?
[243]
Six; because M is the sixth
position in this name.
[248]
And if I switch this to a
letter that's not in the name,
[252]
I get an error, right?
[254]
So basically, I get either
a number, if it's in there,
[258]
or an error if it's not in there.
[261]
Now let me just apply this
to the full range here,
[264]
and this is where we can see the advantage
[266]
of a new dynamic-array-aware Excel.
[269]
So instead of looking for this in A2,
[272]
I'm going to look for
it in the entire range,
[275]
so control-shift down, and
then I'm going to press enter.
[278]
And my formula spills.
[280]
So it gives me all errors,
because all of these
[284]
don't have V in there;
this one does, and that's
[287]
in the fourth position,
and the rest don't either.
[290]
So if I switch this to Rob
now, we can see numbers
[295]
and errors, so what I wanna
do is to convert these numbers
[298]
and errors to true and falses.
[301]
So if it has a number,
it should be a true,
[305]
and if it doesn't have a
number, it should be a false.
[308]
So I can use the IsNumber function here.
[311]
And just wrap this up in there,
[314]
press enter, now I get my
false and true values in here.
[318]
So now we can move on to the next function
[321]
that can filter this list and
just give us the TRUEs back.
[327]
And a great formula for
that is a new dynamic array
[329]
formula called the FILTER function.
[332]
We need to define the array
that we want filtered,
[335]
so basically, what do we
want to see back here?
[338]
We want to see a list of customer names,
[340]
so control-shift down to
select the whole range.
[343]
The next argument is:
what do we want included?
[345]
And we already have our
true and false values here.
[349]
So filter is just gonna include
the true values in here.
[354]
And the last argument is:
what do we want it to show
[357]
if it's empty, so if it
doesn't find anything?
[360]
I'm just gonna put: not found.
[365]
Okay, so when we type in
Rob, we get the list of names
[368]
that include the word Rob.
[370]
If I type in V, I just get one,
[374]
and if I type in something
that's not there,
[376]
I get: not found.
[378]
Okay, so that works well.
[380]
It's just that we're not
gonna be typing here.
[383]
I'm actually just gonna call this:
[384]
data validation prep.
[387]
But instead, I'm going
to change that reference
[391]
to search in here (Report tab).
[394]
So in that cell, I had GAR,
[396]
and I get my data validation
preparation list,
[399]
showing me the names that include GAR.
[402]
Notice that my customer
list here is a list
[405]
of unique values.
[407]
If yours isn't unique, so
if you have Robert Spear
[411]
mentioned a few times, you
wanna get the unique list back,
[415]
all you have to do is wrap this up,
[417]
inside the unique function.
[420]
But in my case, I don't need to do it,
[422]
because I have a list
of unique values here.
[425]
But also if I wanted this to
be sorted, I can also wrap
[430]
this up inside the sort function.
[434]
Okay, so actually, let's do that.
[436]
Now as a next step, all
I have to do is to get
[440]
my drop down in here, so
I'm gonna go to data,
[444]
data validation, under settings,
[446]
I'm gonna select list, for source,
[449]
we're gonna go to master data,
[451]
we're gonna click on the first cell
[453]
that has our main formula,
[455]
and we want our entire spill array,
[457]
so the entire spill range, I
need to put a hashtag (#) in there
[461]
and I say: okay.
[462]
So when I click this,
I get the list of names
[466]
that include GAR.
[467]
Now, there is one setting
we need to change,
[469]
because if I type in something
new and I click this,
[473]
I get an error telling me
this value doesn't match
[476]
the data validation restrictions
defined for this cell,
[480]
right, because it doesn't
find Rob in that list.
[483]
So I'm gonna click on cancel
and go back to data validation.
[487]
Under error alert, I need
to take away the check mark.
[491]
Show error alert after
invalid data is entered.
[495]
And I say: okay.
[497]
So now if I type in Rob
and I click on this,
[500]
there's no alert.
[501]
My data preparation list updates.
[503]
So does my data validation list.
[506]
Now the aim is also to get
the company that this person
[509]
works for; now here you
can use VLOOKUP,
[512]
you can use INDEX and MATCH, but since FILTER
[515]
is one of my favorite formulas right now,
[518]
I'm gonna use that.
[520]
So our array is company, right,
[523]
because that's what we want to get back.
[526]
What we want included is
the customer that equals
[530]
the customer that we select right here,
[535]
and as the last argument, if it's empty,
[537]
I'm just gonna put: nothing ("").
[540]
Close and enter.
[543]
Now if I'm just typing
something in like M,
[547]
and I click here, notice that
it doesn't return anything.
[551]
Right, because I said
if it doesn't find it,
[553]
it should show nothing.
[555]
And then once I click something,
then it shows the company.
[559]
Now if I look for something
that's not on the list,
[562]
I also get nothing, and
when I click on this,
[564]
it just says, okay, Leila's not found.
[568]
Now, let's go to the part where
we can add in new customers
[572]
and get our list to automatically update,
[576]
without us having to do anything.
[578]
So we're going to take advantage
[580]
of Excel table functionality.
[583]
So all I have to do is turn this data set
[585]
into an official Excel
table, so you just click
[587]
anywhere inside and press control-T.
[590]
My table does have headers.
[592]
Just click on okay, and just
gonna go and clear the design.
[597]
And call this table: customer.
[600]
And press enter.
[602]
I don't really have to
do anything anymore.
[604]
All I have to do is just
go and add in my names.
[608]
And let's go to my report, type in Leila,
[612]
and click on this and I get Leila Gharani.
[615]
And this formula updates
automatically as well,
[618]
because it understands
that the source data
[621]
has changed into an official table,
[624]
so it expands the formula range as well.
[627]
Now I know it can be
frustrating if you don't have
[629]
dynamic arrays, but if you
have Office 365, it's coming.
[634]
And if you're excited
to get dynamic arrays,
[636]
click that like button.
[638]
Once they're there, you're gonna
be one of the first to know
[641]
how to create searchable
dropdown lists, without VBA.
[644]
I'll keep you updated, so
if you're not subscribed
[647]
to this channel, consider subscribing.
[650]
(uptempo music)
Most Recent Videos:
You can go back to the homepage right here: Homepage