Instructor: Next up, let's talk
about the SORTBY function.
This one's very similar to SORT
with one very important distinction, which is
that it can sort an array
of data by columns that live somewhere else.
They don't have to live
within that initial referenced array.
So the syntax looks a little bit different here.
With SORTBY, you've got your initial array
that's the cells that you want to sort.
But the second required argument here is the by array
and that's the array of cells that you want to sort by.
This is very similar to the index in a SORT function
except here we can reference a completely different range
of cells to use as our sorting criteria.
And then we've got some optional arguments here as well.
We've got the sort order,
same story where one is the default behavior ascending
or we can use negative one to sort descending.
And then finally you've got any additional pairs
of arrays that you'd like to sort by.
So just like in our last example,
if you wanted to sort by one column then another,
SORTBY accommodates the exact same behavior.
So quick example here.
Imagine we've got that same product level data
that we looked at with the SORT demo.
We've got products in column A,
we've got sales margin and profit
and we wanted to use a SORTBY function
to return just the product and sales data.
But here's the catch.
We don't wanna sort by either product or sales.
We actually wanna sort by profit, which lives in column D.
That's where SORTBY can help us out.
So in this case, the resulting array from F2
through G10 is sorting the data from columns A and B
but it's sorting them descending by profit
which lives outside of that original array.
So essentially this just gives our SORT function
a little bit more flexibility in terms of the data
that we want to return in our spill range.
Now let's jump back into Excel
and practice giving this SORTBY function a shot.
Alright, so back in our section nine workbook, we are now
in the third tab, the SORTBY tab.
We've got the same raw data that we were dealing with
with our SORT examples, and we're gonna start
by producing the same output that we wrapped up with
in our last demo.
We wanted the top industries by growth rate first
and then average salary both descending.
So just like we did in the last demo,
we could use a SORT here
because both those columns, average salary
and year over year percent growth exist
within the source array that we're sorting.
But we could also use a SORTBY
to accomplish the same thing.
And let's do that just so we get comfortable
with the syntax here.
So remember that first argument is the array of data
that we want to return
and sort here in our spill range.
And in this case, we want all of the data to be returned
because we want all three columns represented here.
So A3 through C19.
And the first column that we wanna sort by
is the growth rate.
So I can select C3 through C19.
The sort order for growth rate is descending negative one.
And then the second column that we wanna sort by
is average salary in B3 through B19.
And let's also sort this one descending
with the negative one.
Close the parenthesis, press Enter.
And there you go.
We get the exact same results that we saw
in our SORT demo.
Growth rate is descending.
And anytime where growth rates are equal,
we now see salaries descending as well.
So that looks good.
But again, we could have used a SORT here if we wanted to.
This would've just been a matter of preference.
Now here's an example that shows us
why SORTBY is actually so powerful.
Let's say you wanna return a list
of industries sorted by salary descending
but you don't wanna include the salary data,
just the industries themselves.
This is where you'd have to use SORTBY.
So let's go ahead and start with the SORTBY function.
The array, remember is the data or the scope
of the data that I want to be returned
in my spill range, which
in this case is only the list of industries, nothing else.
But I want to sort those industries
by a different field that lives outside of that array.
In this case, our salaries in column B,
B3 through B19.
Again, I want these descending.
I want the high salary industries at the top
so I can put a negative one, close it out, press Enter,
and there you have it.
Law is the industry with the highest salary.
Food services is the industry with the lowest salary.
So again, great way to create sort logic
and sorting behavior with a little bit more functionality
and a little bit more flexibility than a standard sort.