okay so in this video i'm going to show you how we can find the first
second third or nth highest match we'll do that both in google sheets and excel
and it's actually going to be most likely exactly the same formula so let's just
start here so first of all let me explain you what i'm trying to get so let's say we have this data
and we have all the sales people i want to find out for example for isabella
when was the first the second or the third or m highest sale to illustrate that if i just took
this data set and sorted it that's not the way we're going to do that but this is just
explaining you what we're trying to accomplish here if i just take the header rows and just sort
this data first by sales rep and then add another level and sort it by sales amounts and i'll do z2a
highest to lowest for sales amounts right so if i sort this data and we scroll down to find isabella
see we have all isabella sales grouped together and we also sorted her sales amounts so this is
the first highest then the second highest and it's gonna keep going and see it's decreasing because
of the sorting order and by the time we get to the last number it's the lowest number and then we got
jake so for isabella if i was trying to find her second highest sale date that would be this
date right in here if i wanted to find the first highest one it would be this one and so on right
so that's exactly what we're gonna do so i'm gonna undo all of this sorting
and we'll do this whole thing with a formula and we'll do it in a way that we could just list all
the sales people and find when was their highest sale date so first of all let's just get a list of
our sales people so i'll start by doing unique function and just select our salespeople from
here so i need the range of sales people from here so i'll just click on the first sales person ctrl
shift down or command shift down to select this whole range close parenthesis hit enter that
should give us a nice list of sales people you could sort this if you wanted to by adding sort
function around this like that i'm not actually going to sort this but if you wanted a sorted list
you could do that too i'm gonna get rid of that so now we need to find the highest bond for isabella
so to do that what i'll do i'll first filter the data i have on the left to just isabella
and i'll probably do it right here so it doesn't sit right next to each other so it's
not too confusing so i'll do filter and what i'm going to choose as a range for this filter
is this whole data set so i'll just select from basically a through g all the columns in my data
just for good practice i'll do f4 to make this absolute reference comma and then i need what's
called a condition in this function so i want to filter this data to just isabella right now so
the way i'm going to do that after this comma i'm going to select this column c that i want filtered
by and i'll do f4 to make it absolute reference and i'll check if that equals to isabella right
in here close parenthesis and hit enter and what we're going to get is right here see filtered data
from this data set on the left just filtered data that shows isabella's sales so now from this data
i want to make sure i have this sorted by the sales amounts to have the highest one on top
and to do that we'll take this filter function and add sort around it and sort function will allow us
to sort this data so this is going to be the data we're trying to sort i'm going to go after this
parentheses do a comma and the column i'll be sorting by we need to provide the column number
so in this filter data it's going to start from here one two three four five six seven column
number seven is our sales amounts right comma and finally do we want to sort this in ascending order
or not so if you want in ascending order you'll do true so let me just show you what it gives you
so i'm going to do true and hit enter so you'll see how it sorts by these numbers from lowest
to highest we want the exact opposite so what i'm gonna do i'm just gonna change this true to false
so we're sorting by column number seven in descending order
and there we go we got the highest number on top so if i wanted to know the date then it would be
this date right here if i want the first highest one this would be the date for that if i want the
second highest one this would be the date for that and so on so let's start with the first
one so if i wanted the first highest one i'll go back and we want to basically just get this date
that's in this grid located in row one column one right in here if i wanted this one that would be
row two column one in this grid now if you wanted to get maybe like the store or something then you
could think about row one column four or row 2 column 4 or something like that in this case i
want the date so it's going to be row 1 column 1 if i want the first one so what i'll do i'll go
back to this and put this inside of a function index and index will allow us to do that exact
you know indexing of our data by giving wrong column numbers so the row is going to be 1 comma
and the column is also going to be 1. so i'm going to do that both one i'm gonna hit enter
and that should be the date it's not formatted right so i would have to change this formatting to
a date because this is a date so that would be the date for the first highest sale so
if i wanted the second one all i have to do is change the row number to go to the next
row down and that would be this one two and this would be the second highest sale date
and so on in this case i'm gonna do the first one and you could obviously change this three
four five whatever you want right i'm gonna do the first one one one and that should be the
first one for isabella i'm gonna delete this one for now and then if i wanted now to get that for
everybody else i could just drag this formula down and we're gonna get the first highest sale
date for each salesperson so let's just check for example one of these let's say i want to check if
this is correct only via 3 30 2018 right so to do that again we need to make sure we sort this data
so again let's just keep that in mind 3 30 2018 so march 30th so i'll take this and sort this data
first buy sales rep and then buy sales amount
highest to lowest and let's try to find olivia here
so there it is see that's the first one and that's 3 30 2018
which is accurate and the second one should be 22 2019 so if we go up there
and change this to
2 instead of 1 see that would be 4 22 2019 again i'm going to change this back to
the first highest drag this down and as you can see it works so to do the same thing in excel
it's gonna be pretty much the same formula so i'm gonna just copy this unique function
exactly the same way go back to excel and just put this in here someplace so that's our list
of sales people i'm gonna go back to google sheets let's get our second formula and see if that works
i'm gonna copy that hit escape go back to excel i'm going to paste it right in here
so we got an error let's take a look and see what's going on filter should definitely not be
our problem let's take a look at the sort function so this sort let's take a look at their arguments
they have array sort column and sort orders so this would be the column so column is going to
be 7 well that is not going to change what is the sort order maybe they don't accept false
and true let's just do a comma here uh and it's blocking some of this but i can see descending see
it seems to be negative one here so i'm gonna do negative one instead of false and let's
try this and see if that works seems like we're doing well now so i'm gonna change this to date
there we go let's just make sure it's the same thing so isabella 10 14 18. so isabella
10 14 18. not in the same sorting order as far as the people go but the data is working the same now
apparently in excel when you do this sorting order see right in here see ascending is one descending
is negative one in google sheets that would be true or false so instead of saying false here
we'll just do negative one and that should take care of that and that should do it for this video
thanks for watching please subscribe and i'll see in the next one