so i did a video to find the second third fourth etc match if you wanted to from a list of matches
and i had a question how to actually create something that will get us the last match so we're
gonna do that again to work in both google sheets and excel in this case so i'm gonna go to google
sheets the formula is gonna be the same for both so i'll copy and paste it here in excel once it's
done so we can verify that it works so i'm gonna get back to this so basically what we have here
is a list of company ids and for each company id from this contacts we have possibly no matches or
one or more match so for example if we look at this id that ends with three three zero if
i copy that if i go in here and search see we find one two two matches from this list
so in this particular case we would want to return the second match and in this case i'm gonna do
names but you could do anything you want right from here now the same way if we go to the second
id see we have one two three four so in this case we need to return this last person from this list
so we need to always find the last match so to do this i'm going to go to companies here
and i'm going to start with filter function again so i'll do equals filter and what we'll do as a
range for this filter we'll go to contacts spreadsheet and basically get the list of
names that we need returned so in this case that's going to be in this b column i'm going to select
the whole column b press f4 to make this absolute reference comma i want to filter by true and false
statement that i'm going to get by taking this column a with this company ids again f4 to make it
absolute reference and check if it equals to that list of company ids go back to companies and click
on the first company in the list this one and close parentheses now if you're in excel you come
back and you click on this id it's probably going to add the name of the worksheet here in front of
a2 you want to go ahead and remove that just keep this nice and clean like this just the reference
to the cell without the worksheet name here that says companies so at this point if i hit enter
see i will get matches to this company id so again if we just go check this three three zero zero see
here is one here is two and those are the names we return right in here so out of those names i need
the second one so i would have to just go in here and do index open parentheses and then i'm going
to go all the way to the end comma and give it the position of the item i need returned so that
would be 2 in this case the problem is with that 2 is that only for this particular case for this
company that two is valid because sometimes it's gonna be two matches sometimes it's gonna be four
sometimes it's gonna be three so we always need to figure out which one do we need to get so in this
case it's gonna be two in other cases it's gonna be something different so for example if i just
drag this one down see this is not going to be accurate most likely because if we check this id
and search in this list there seems to be one two three four matches so to get the last one this one
that should be position number four in here so as you can see we can't really
drag this formula just down just yet to get the results we need because of that problem
that position is going to change sometimes it's going to be 2 then it's going to be 4
then it's going to be something else so now we need something that will get us those positions
well what we can do we can create a logical statement in sumproduct function to find how
many matches we're actually able to find to give you an example of that i'm going to do right next
to this we'll incorporate it in our formula in just a second i'm going to use sum product
because that's going to make my life easier to not have to do array formula brackets around this
so what i'm going to do as the array i'm going to go to contact and select this column a of
company ids i'm going to do f4 to lock the range and i want to check if that equals to
this range to the company id in the current row so again i'll go back to companies so i'm gonna click
again on this company id on the left close parentheses so again if you're in excel when
you click on this id it's probably gonna say companies exclamation and then the reference
make sure you get rid of that because otherwise you won't be able to sort your data later on
so i'm gonna do this and hit enter so let's see what it gives us i'm gonna just let it do
whatever it does see it gives me two here four here one here so this is actually giving us how
many times it was able to match this id so what's happening is that we get this list of trues and
falses when we do this logical statement and every time it finds the same company id it gives us true
and every time it doesn't it gives us false so if it finds it four times it's gonna have four
trues in this logical statement and sum product is gonna take those trues and convert it to one
and it's gonna add them up together so it's gonna get four now in this first line there are two of
them so it's gonna get two truths and two truths is gonna basically take it as two ones and it's
gonna give us two right here so the total is two and then four and two so this gives us how many
matches we have and that's exactly what we need here see we have two here then we have four here
and so on so zero means there was no match for this so if i go back copy this and go back to this
contacts and search see zero matches it's not able to find any match for that one so there's nothing
we can do about that one there's no match so all i have to do now just go back copy this sum product
formula without the equal sign hit escape go back to my first formula and replace this hard-coded 2
with that sum product formula that gets me the position and with this i'm going to hit
enter and drag this formula down i don't need this anymore and there we have it that should give us
the last match so at this point i can just do if error to handle any errors if i don't want
those n a's to show up there and i'll do if error and in the end i'll do comma and double quotes
to make it blank in case there's no match and drag this formula down again and there it is so let's
just check a couple of these results to verify they actually work so let's start with this one
well that one i remember that's actually correct let's do this one so that should be the latest
match for this so let's take a look yep that's accurate that's the latest match let's do this one
apparently there's only one for that which is this so that's accurate as you can see
let's try this one too
so see we got one two three matches and the third one is this one the latest match and
that's exactly what we got so we're good on this now let's just finally verify that this
same formula works in excel so we're going to copy this hit escape go back to our excel worksheet
i'm going to go to the same tab here and just put my formula right in here
hit enter so we seem to have a problem here because that returns two results for some reason
so let's see what's going on here it doesn't actually return us the match we need so what
i'm gonna do i'm gonna quickly test the sum product formula on its own so i'm gonna copy
that hit escape go back here and do equals and sum product so that gives me zero so apparently excel
doesn't treat this as true and false in a correct way so let's just do a quick conversion here so
i'm gonna put this contacts and this equals a2 in parentheses and then add double negative to
convert them to ones and zeros explicitly so see now i get my 2 4 1 etc that now works
so basically what i would have to do here to make this same formula work in excel i'm going
to go back to this and see how i did this sum product right here and i got this contacts array
right in here so i'll just take this contacts list and put it in parentheses from both sides
so now i have one extra parenthesis here and an extra parenthesis in the beginning that jumped
over here and in front of that i'm just gonna do double negative to make sure i convert that to
ones and zeros hit enter and now we should get the same results so if i look at this then go
back to our google sheet we should have the same results and by the way that excel formula
that worked here should also work in google sheets so while google sheets formula apparently had a
little problem working in excel we can copy this formula and we should be able to put that same
formula here in google sheets with those double negatives and it should still work just fine
here so there it is we have now one formula compatible for both and that should do it
for this video thanks for watching please subscribe and i'll see you the next one