all right so in this video i'm gonna show you how we can find second third fourth or whatever
match you want from a list of matches and we should be able to do this on both google sheets
and excel assuming you have the version of excel that has the functions that we need if you look
here i have this list of contacts and we should be able to find this company id
in this first column more than once so if i just go ahead and copy one of these and just search
see there's one two three four five these are different contacts from the same company
and the company id is right here i'm gonna show you how we can find the second match or the
third or the fourth or the nth match basically from a list of matches now if you're trying to
find the first match you would use a vlookup if you're trying to find all of the matches i have
a video covering that but in this one let's say you just wanted to get the second or the third
something like that so to do that let's go to this company's list and we should have a company
id here or company number so if i was searching for this number for example in this contacts
see there's one there's two apparently there's only two for those
so if i'm trying to find the second match what i would do i would go back here and
what i'm gonna do here i'm gonna do a function called filter so i'm gonna do equals filter
and here we need to provide a range so that range is going to be what you need returned
after you find the match so that means if we for example search and find
this is that match right do we want to then get the results from this names column email column
or department or what is it gonna be so let's for right now assume we're gonna get the name so what
i would do in that case i would just select this list of names now in google sheets i can just drop
the end reference like this in excel you wouldn't be able to do that so you could just go for the
whole column like this let's make it compatible for both so i'm just going to select this whole
column b in this case that's names comma before doing comma let's actually do f4 for good practice
to make it absolute reference comma and then i want what's called a condition so this condition
should be an array of trues and falses and we can get that array by selecting this ids
and again i'm going to do f4 for absolute reference for this and then i'm going to do
equals so i want the ids that match i'll go back to companies and click on this first company id
which is in this a2 cell so if i close parentheses and i hit enter
see what this function is going to return is the list of names from
here that match that id so if i just go and search for this id right here
see we have these two matches and these are their names see we have wesley we have this guy so if we
go back and take a look here they are both now i want in this case the second match which is this
so what i would do i would just go back here and put this filter function inside of index function
like this and index function will allow us to select the position from which we want to get the
results so i want the second one so i'm gonna do two so this should return the person we actually
want so see the second match would be this one now if i wanted to get the first match then instead of
two i could do one now for the first match you could also do a vlookup instead of doing all
of this but see it will work and then you can just change this to any number you want right so if you
wanted like the third match you would do three and i don't think we had a third match for this so see
we get an error because there's no third match at this point if i just take this formula the way it
is and just drag it down we should be able to see third matches wherever they are available
so for example for this one if we go check that should be chase so let's go and take a look
first second and third see that's chase so that's what we got and let's just check one more so let's
take a look at this one and that should be marvin so let's go back and take a look so one two three
and third one is marvin as you can see works just fine and if you wanted to handle those errors then
we can just take this and put this inside of if error function like this and close parentheses
in the end so basically what this is gonna do if it's an error it's just gonna leave it blank
so if i wanted to get the second match then i can change this to two the index and drag
this down and that should give us all second matches if they are available so that's that
now this same function should be possible in excel with one difference so the last piece i did here
for iferror excel doesn't allow you to do optional if error here in this case what i did
in google sheets i just skipped this value if error and basically just leave it blank in excel
you can do that so what you can do here you can do comma and do double quotes to just leave it blank
and that should work in google sheets as well
just like that let's just test this same formula in excel to verify that it works so i'm going to
copy this hit escape go back to excel this is the same file we had from there i'll just go ahead and
paste right in here and hit enter and drag it down see same formula works in excel and google sheets
to get us the second or whatever match you like in this case second you can change it to
again three to get the third match if it's available like that and so on and that
should do it for this video thanks for watching please subscribe and i'll see in the next one