How to Use the SORTBY Function in Excel

Welcome to our guide on using the SORTBY function in Microsoft Excel and Google Sheets.

Introduction

The SORTBY function enables sorting a range or array according to the values in a corresponding range or array. This is particularly useful for organizing data according to criteria specified in a different range.

Syntax

The syntax for the SORTBY function is consistent across Microsoft Excel and Google Sheets:

=SORTBY(range_to_sort, range_to_sort_by, [sort_order1], [range_to_sort_by2], [sort_order2], ...)

range_to_sort: The range of cells you wish to organize.
range_to_sort_by: The range of cells that determines the order of the range_to_sort.
sort_order: Optional. Use 1 for ascending order and -1 for descending order.

Examples

Sorting Student Scores in Excel/Sheets

Consider a dataset of student scores as shown below:

Student	Score
Tom	85
Amy	92
John	78
Lisa	88

To sort the students by their scores in ascending order, use the SORTBY function:

=SORTBY(A2:B5, B2:B5, 1)

This will return the students sorted by their scores:

Student	Score
John	78
Tom	85
Lisa	88
Amy	92

Sorting Data in Multiple Columns

For scenarios requiring sorting by multiple criteria, such as sorting by both age and name, you could apply SORTBY as follows:

=SORTBY(A2:B5, B2:B5, 1, A2:A5, -1)

Conclusion

The SORTBY function offers a robust mechanism for sorting data in Excel and Google Sheets based on secondary values. It is versatile in accommodating various sorting requirements and methodologies. Understanding and utilizing SORTBY facilitates effective data management and analysis in large data sets.

More information: https://support.microsoft.com/en-us/office/sortby-function-cd2d7a62-1b93-435c-b561-d6a35134f28f

Other functions

ADDRESS

Returns a reference as text to a single cell in a worksheet

AREAS

Returns the number of areas in a reference

CHOOSE

Chooses a value from a list of values

COLUMN

Returns the column number of a reference

COLUMNS

Returns the number of columns in a reference

FILTER

Filters a range of data based on criteria you define

FORMULATEXT

Returns the formula at the given reference as text

GETPIVOTDATA

Returns data stored in a PivotTable report

HLOOKUP

Looks in the top row of an array and returns the value of the indicated cell

HYPERLINK

Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet

INDEX

Uses an index to choose a value from a reference or array

INDIRECT

Returns a reference indicated by a text value

LOOKUP

Looks up values in a vector or array

MATCH

Looks up values in a reference or array

OFFSET

Returns a reference offset from a given reference

ROW

Returns the row number of a reference

ROWS

Returns the number of rows in a reference

RTD

Retrieves real-time data from a program that supports COM automation

SORT

Sorts the contents of a range or array

TRANSPOSE

Returns the transpose of an array

UNIQUE

Returns a list of unique values in a list or range

VLOOKUP

Looks in the first column of an array and moves across the row to return the value of a cell

XLOOKUP

Searches a range or an array, and returns an item corresponding to the first match it finds If a match doesn't exist, then XLOOKUP can return the closest (approximate) match

XMATCH

Returns the relative position of an item in an array or range of cells