all right so in this video i'm going to show you how we can build more intelligent sort of search
in our spreadsheet and we'll be using this data as our source data and this match score is a column
that we're going to create but the way this works is that for now see if i do adidas running shoes
and i did match all so you'll see that any product from that list that matches all these three words
it's gonna return in results if i do match any you'll see that it will return anything
that has the word adidas running or shoes and if i do something like adidas nike
running shoes and let's just change this because right now we say any let's do match all so you'll
see there are no results because here we either have adidas running shoe or we'll have like nike
running shoe so none of those have all those words as a match to return but as you can see we also
have this scoring system where we can get like it's 75 match or 50 match or something like that
and using that scoring system see we can go here and do match closest and now we can get see
it's kind of close to matching this and close to matching that so it returns both of those results
and we can redefine what those thresholds are using this options table where we can say these
are the options and these are the percentages for those different types of matches and that's pretty
much what we're going to do here so i'm going to start building the formula next to this and then
i'll move it to a different tab so i'll go ahead and for now hide some of these columns and that
should allow me to zoom in more so i'll go ahead and type something that i'm searching in the cell
so what i want to do i want to basically take all of these words and split them by
space and then see how many of those i can actually find in these cells right here so
the first thing i'm going to do i'm going to do equals trim and point to the cell
which is where my search value is going to be and close parentheses and the reason i'm doing this
is just in case we have let's say multiple spaces here i don't want to actually use
those to do anything i want to remove those so that's what trim does it's gonna remove
any extra space in the middle and also before and after if we have extra spaces so if you
don't wanna do that you don't have to i am going to actually apply that now that should take care
of those empties so once i trim that now i'm gonna take this text right here and i'm gonna split it
by this space character in the middle and the way i can do that i can just use split function
and go after this whole thing do a comma and then as a split character i'm gonna do
quote space quote so basically a space if i close parenthesis and hit enter
you'll see it will split this to three words so now i wanna search for each one of these words
in here let's say in the cell and see if i'm able to find those words so for that i'm just gonna do
it separately and then i'll combine it to a single formula i'll just go here and do match
and as a lookup value for the search key the first match option usually you would just provide
one of these so actually let me do this with one so you can see how it works so if i do just nike
as a lookup value comma and then click to search within this text over here comma and then as a
search type i'm gonna do zero exact match if i'd enter well it's not gonna work because there's no
nike first of all but even if this wasn't a nike let's actually go change this to let's say adidas
you'll see it's still n a because it's searching to be exactly adidas for this whole string
i want to see if it contains adidas so to do that i'll go back here and add a wild card so code
starcoat code concatenate a wild card ampersand to concatenate from the beginning and then
i'm going to do another wild card in the end like this and now if i do this and hit enter
you'll see that i get one because this text contains adidas now if i go back and change this
lookup value instead of just one cell here to be this whole array these three cells and because
this is gonna be an array formula i'm gonna do control shift enter or command shift enter
to get array formula around this hit enter and you'll see i got one one one and that's because
all of these three boards are actually here now let me go change this adidas to nike and
you're gonna see now that nike is not here so we get n a for nike but running and shoes are
here so it will get us one and one now what i want to do i want to replace this n a when
it doesn't find with a 0. so what i'll do i'll just wrap this match function inside of if error
and i'll say if error go after this comma i'll do a zero just like that so if there's no match
let's just replace it with a zero and all of these will be ones so now if i wanted to just
combine all of these together i could just take this formula right here copied hit escape go
back to this formula and instead of referring to this range i can just replace the range with that
formula right there that function split paste it right there hit enter and it's
still going to work it's going to give us 0 1 1 and then i don't really need to keep this
over here anymore so we got 0 1 1. so now if i wanted to get how many i was able to match
i should be able to just sum this so if i do sum and close parentheses right there
hit enter it will tell me two words match here if i change this to let's say
s9 see it's going to tell me three words match because we have s9 shoes running if i add adidas
this is not case sensitive by the way but it is sensitive to this sort of thing i would have
to type this correctly see now we have four words and all four words actually match here
but if i go here and change this to let's say adidas and s two then it's only 3 that match so
you can see we get how many words out of this string match to this particular text over here
so now if we take that and divide it by how many words we actually have in our text we can say
it's like percentage match so for that i need to get how many words we actually have in the search
string and we can do that by doing equals again we'll do trim to make sure we remove any extra
spaces from here just in case and then we can just split again using that same space character and
if i hit enter you'll see we have four words and finally to get that four i can do counter
to count how many things are not blank basically and we get a four so at this point if i take
this formula copy this hit escape and go back to this formula and after this whole formula is over
still within this array formula i'm going to do this divide and then paste that other formula if
i hit enter i will get my percentage i can switch this to some sort of percentage formatting so now
we're saying 75 of words here match so if i delete this now i go here and do adidas running shoes
just like this we're gonna have three boards and all three match so we'll get hundred percent match
so all to rewards match and then as you can see if i do a different thing like nike will get 66.67
okay so now in this formula we basically link to this cell and this cell so this cell i'm going
to lock in place the one that we type what we search so that's k2 so we have a couple of those
references so i'm going to take this one and do f4 and then i'm going to do this one and do f4 or you
can do those dollar signs manually it's up to you so once i lock those references i'm going to hit
enter and then i'm going to do ctrl x or command x to cut and i'm going to do control v or command
v to paste right over here so i'm going to just move that basically in the spot and the reason i'm
doing that is because now i should be able to just drag this formula down and essentially it's going
to give me some percentage see it's going to tell me like this is the percent i was able to match
and for this one see i was able to match hundred percent and then there's another one apparently
hundred percent of that i was able to match so now i should be able to use this percentage
type of scoring to decide which ones i would like to return in my results but before i do that
let's do one more thing so if i keep dragging this formula lower this should be fine we could return
zeros but maybe if there is a blank we just want to return blank in those cases so i'm just going
to do that to clean this formula a little bit so i'll go on top here i'm going to work with
the first formula and right there i'm going to say if open parentheses this cell equals blank
that b2 cell comma then we would like to just return blank comma in all the other cases i would
like to do that sum thing that i was doing i'm gonna go ahead and close parentheses right here
another one hit enter and now we should be able to just drag this as long as we wish
just in case we're gonna be adding more data so i'm just gonna call it match score
and finally now i want to move this search to its own tab
so i'm going to create the tab i'm going to call it search here you can call it whatever you want
and then i'm going to go to products i'm going to cut this cell with our search text
command x or control x go back to search here and paste it in one of the cells right here the
reason i did cut instead of copy is because when i do cut it will automatically adjust this formula
now see it refers to that b2 cell from that search here tab so i don't have to redo all of this thing
so i can now open this data those hidden columns and this is my data set and now i have
a score in this column and as i change this
my score should change over here see 100 and 100 for these adidas results so i'm going to go ahead
and call this search it's just text doesn't matter this is where we actually do the search
so now based on this i would like to return my results what i'm gonna do here i'm just gonna add
a filter function and in this filter function i'm going to go to products and i'm going to select
all of these columns except that match score if you wanted to return the match score to
feel free to select that too i don't want to return that so i'm gonna do this and remove eight
to go all the way down comma and then we need the condition so as a condition i'm gonna select
starting from the same position row 2 this match score column a little bit i'm going to drop the
end reference for that too just so that you can see this is where those percentages are located
now first of all i want to make sure that anything that's 0 percent match it's not returned so i'm
gonna say that is greater than zero comma and then i'm gonna do another one i'm gonna select
that same score column and again drop the end reference and then i'm going to say it should
be greater than and this is where we're going to define what that cut off percentage should
be so for example i could say i want 75 percent let's do greater than or equal to
so with this i'm gonna close parentheses and hit enter and see how i get three results
and basically those three are the ones that are above 75 percent here so we have one two and three
and now i can just copy this column names paste it
right here apparently we have another column name there that i didn't copy here we go
if you didn't want to return all the columns you could just select some of them make this bold so
this now works pretty well the last thing i want to do to this i want to make sure that i have some
sort of choice for this match score so instead of just hard coding 75 maybe we'll do a drop down
here to choose between match all close match to a few different options here right so the way i'm
going to define those options i'm actually going to just do another tab here i'm going to call this
options and basically i'm going to create my lookup table here so i'll say option score
so as an option let's say we're going to have match all
which will be basically if we have 100 percent match for all words we're gonna say match closest
and the nice thing about this is that we could come back and change these
percentages if we wanted to right so i'll do 75 or something and then i'm going to say match any
and for this we'll do just zero percent and the reason i can get away with zero is because in my
other filter i did that it must be at least above zero so that should work out so here
you can define more like percentage options and more options as you wish i'm just gonna go ahead
and do this three so now first i wanna just add a drop down here so i'll do data validation i'll do
list from a range and as a range i'm gonna go ahead and select from this options
this range of three press okay reject any other input press save and now we should have a drop
down we'll now say match all and what i could do now i could basically just do a vlookup
to match this comma in my options in this range i guess i'll just drop the end reference for this
comma and then column index i'll do 2 and this will be zero for exact match so basically what
this is gonna do if this says match all it's gonna say hundred percent if i do to closest
it will basically grab this 75 and then if i change it to any it will grab this zero and we
can redefine those percentages as we wish so at this point let's just go back to match all
i'm gonna just grab this vlookup formula without equal sign copy that hit escape go
back to this formula over here and instead of hard coding 75 here i'm gonna remove that and paste
that vlookup formula and now i can hit enter i should be getting the same results i'm gonna
delete this formula from here so now if i do match any see it's going to give me any match that has
at least one of those words and since running shoes appears pretty much in all of these
see cases all of them seem to be showing up so if i do something like nike adidas
see match any so basically it matches adidas it matches nike we have our results and that's
that zero percent case basically if there is at least one word that's a match i probably also
want to test what happens if we just leave this blank so match any gives us no results excellent
because remember one of our cases was to at least have greater than zero over here and then finally
if we do something like this some match any should give us this see anything that contains shoes or
adidas if we do match all it's going to give us those if we do match closest then we basically get
that 75 cutoff so if we go back to our data to see what happens in our data with our match scores
see we don't have anything that will match that 75 other than this 100
so now let's do something like nike adidas running shoes and see if i do match closest see all of
this match because if i go to products you'll see that these are like see 75 match so if i also
wanted to return all this 50 but not return as 25 then i could go back to options and redefine this
to be like 50 and then you should be able to see as i do that those matches are returned as well
but i think i'm gonna go back to 75 you can decide what that cutoff should be at this
point just by changing these values so you can see i got those results but if i do match all
it will not get me any results because there's no case when all of these words exist in
our search so there it is thanks for watching please subscribe and i'll see in the next video