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
GA R and I click on this I get the list
of names that only include JR now 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 I click
on the down arrow I get the 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 gonna 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 not any more 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 want to have my
searchable drop-down list once the user
searches for customer they select a
customer name they automatically get the
company the customer works for the list
of 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 going to 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 going to 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 ger I need somewhere a list generated
of names that only include GA are now
currently dynamic arrays works well with
names right so names 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 okay so anytime the
user types in J are in here I want my
data preparation table to give me a list
of customers that include the word GA r1
formula that helps us identify which
names have as a J are included in them
is this search formula so let me just
demonstrate this so instead of switching
back and forth from the report tab to
master data tab I'm just gonna act like
my input field is right here so I'm
gonna be typing in JR and right here I
want to see a list of customers that
include JR but let's see how the search
function can help us with that search
needs these arguments 1st argument is
what text we're looking for so that's
what we're going to be typing in where
are we looking it up we're going to be
looking it up here actually it's going
to be the full list but let me just show
you what search actually returns the
last argument is optional is where do we
want it to start looking at which
position well in this case we always
want it to start to look for that name
from the beginning right from position 1
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 6 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
the new dynamic array of where Excel so
instead of looking for this in a two I'm
going to look for it in the entire range
so ctrl shift down and then I'm gonna
press ENTER and my formulas spells 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't see numbers and errors
so what I want to 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 is
number 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 truth back and a great formula for
that is a new dynamic area formula
called the filter function we need to
define the array that we want filter so
basically what do we want to see back
here we want to see a list of customer
names so ctrl 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 but the last argument is what do we
want it to show if it's empties if it
doesn't find anything I'm just gonna put
not found is when we type in Rob we get
the list of names that include the word
Rob if I type in V I just get 1 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 going to be typing
here I'm actually just going to call
this data validation prep but instead
I'm going to change that reference to
search in here so in that cell I had gr
and I get my data validation preparation
list showing me
the names that include GA are notice
that my customer list here is a list of
unique values if yours isn't unique so
if you have Robert sphere mentioned a
few times you want to 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 the entire
spill erase at 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 jay-ar 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 and their error alert
I need to take away the check mark show
error alert after invalidate is entered
and I say ok 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
lists now the aim is also to get the
company that this person works for now
here you can use vlookup you can use
index image 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 em 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 but if I look
for something that's not on the list
I also get nothing and when I click on
this it just says kalilas 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 right so we're gonna take
advantage of Excel table functionality
so all I have to do is turn this data
set into an official Excel table so just
click anywhere inside and press ctrl T
my table does have headers just click on
OK I'm 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 Lana granny 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 you 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
drop-down list without VBA I'll keep you
updated so if you're not subscribed to
this channel consider subscribing
[Music]
you