## #1 – The SORT & SORTBY Functions

### Using the SORT Function

The **SORT** function allows you to dynamically sort your dataset results based on a column within the data.

To understand the **SORTBY **function, the syntax is as follows (*parameters in brackets are optional*):

`SORT(array, [sort_index], [sort_order], [by_col])`

**array**– is the range or array you want to be sorted. This can be the entire data set or part of the original data set.**[sort_index]**– is the column index position (*read left-to-right in the data*) which you want to sort by. The default response is**1**(*left-most column*).**[sort_order]**– defines the sort order for the column selected in the**[sort_index]**For an ascending sort, enter the value**1**. For a descending sort, enter the value**-1**. The default response is**1**(*ascending*).**[by_col]**– is used when your data is not in a traditional table format, where columns are the categories (*fields*) and the rows are transactions (*records*). This would be used when your data is set up in the opposite format; rows are categories and columns are transactions. This is an option that you will likely never use or use infrequently at best. To invoke this argument, enter the value**TRUE**. The default response is**FALSE**.

If you wish to sort by more than one criterion (*i.e., primary ascending sort by Name, secondary descending sort by Salary*), array notation will be required to define the multiple “sort_index” and “sort_order” levels. Example:

**=SORT(DataSet, {1,2}, {1,-1})**.

Take for example this dataset with names, dates, and salaries.

This table has been turned into a proper Excel Table and given the name “**TableSalary**”.

Our objective is to sort the list in descending order by the **Yearly Salary** column. This can be done using the **SORT** function.

Our formula for solving this issue would be as follows:

`=SORT(TableSalary, 3, -1)`

In English, the logic reads, “Sort the table named ‘**TableSalary’** by the third column (**3**) in descending order **(-1**).”

### Using the SORTBY Function

If you need to sort data by a column that will not appear in the result, you can use the **SORTBY** function.

To understand the **SORTBY **function, the syntax is as follows (*parameters in brackets are optional*):

`SORTBY(array, by_array1, [sort_order1]…)`

**array**– is the range or array you want to be sorted. This can be the entire data set or part of the original data set.**by_array1**– is the array of items you wish to sort. This column will not be part of your result.**[sort_order1]**– defines the sort order for the column selected in the**[by_array1]**For an ascending sort, enter the value**1**. For a descending sort, enter the value**-1**. The default response is**1**(*ascending*).

The **SORTBY** function can accept multiple “by_array” and “sort_order” declarations to allow for multi‑level sort conditions (*i.e. sort by country, then sort by region, then sort by state.*)

Using the same dataset as before, if we wish to generate a list of names that are sorted in ascending order, but we don’t want any of the adjacent columns of data, we can write the following formula.

`=SORTBY(TableSalary[Name], TableSalary[Yearly Salary], -1)`

Featured Course

## Master NEW Excel Functions in Office 365 & Office 2021

## #2 The UNIQUE Function

The **UNIQUE** function is useful in the following situations:

- You’d like to get a unique list of items from your data set. The result will include each item from the data set only once in the final list. In Legacy Excel, this required using multiple functions and was quite complicated.
- You’d like to get a distinct list of items from your data set. The result will show items that occur only once in the data set.
- You’d like your unique list to update automatically without the need to refresh a query.

Although features exist in Excel that will provide such lists, like Pivot Tables, these features require manual intervention to update if the source data changes. Because the **UNIQUE** function is dynamic the update occurs automatically.

To understand the **UNIQUE **function, the syntax is as follows (*parameters in brackets are optional*):

`UNIQUE(array, [by_col], [occurs_once])`

**array**– is the part of the original data set that includes your results; the part of the data you wish to derive the list from.**[by_col]**– is the direction you are reading the list for comparison. To read the list across columns, choose**TRUE**. To read the list across rows, choose**FALSE**. The default selection is**FALSE**.**[occurs_once]**– If this is set to**TRUE**, the results list includes only items that occur once in the original data. This can be referred to as a “distinct” list. If this is set to**FALSE**, the results list includes all items from the original data but only one of each item. The default selection is**FALSE**.

Let’s look at some examples of the **UNIQUE** function in action.

We have the dataset below and we wish to generate a unique list of **Regions**.

We can write the following **UNIQUE** formula:

`=UNIQUE(TableDivRev[Region])`

But what if you wanted a unique list of **Division/Region** combinations? The formula would appear like so:

`=UNIQUE(TableDivRev[ [Division] : [Region] ] )`

*(NOTE: Spaces added to the formula to provide clarity; they are not used in the formula.)*

### Generating DISTINCT Lists

Suppose you only want to generate a list of **Apps** where the results are apps that only occur once in the dataset.

The following formula will use a **FALSE** for the **[by_col]** argument (*since we are reading by rows*) and use a **TRUE** for the **[exactly_once]** argument.

`=UNIQUE(TableDivRev[App], FALSE, TRUE)`

## #3 – The XLOOKUP Function

The **XLOOKUP** function is the intended successor to the older, tried-and-true **VLOOKUP** function. **XLOOKUP** is much more flexible and easier to use than **VLOOKUP**.

The logic for the **XLOOKUP** function is as follows:

`=XLOOKUP(lookup_value, lookup_array, return_array)`

**lookup_value**– is the value we want to find**lookup_array**– is the list to find the lookup_value within**return_array**– is the list to return from upon discovery**[if_not_found]**– is what to display if no match exists (*e. text message or default value*)**[match_mode]**– specifies the**Match Type**. ()**0**= Exact match {default},**-1**= Exact match or next smaller,**1**= Exact match or next larger,**2**= Wildcard match**[search_mode]**– specifies the**Search Mode**. ()**1**= Search first to last {default},**-1**= Search last to first,**2**= Binary search {ascending},**-2**= Binary search {descending}

Our objective in the following example is to select an App from the dropdown list located in cell **F3**. From this selection, we want to return the associated **Division** from column **H** and the associated **Profit** from column **C**.

To get the **Division**, we write the following formula (*in cell F4*):

`=XLOOKUP(F3, I4:I13, H4:H13, "Missing")`

### Did you see the hidden awesomeness?

Notice that in the data, the column that we are returning data from is to the ** LEFT** of the column we are searching.

This would be impossible with a traditional **VLOOKUP** function (*without performing some crazy in-memory, virtual table construction which only 9 people on planet Earth find enjoyable.*)

We have also included an argument that issues a text response if the item being searched for is not found in the list.

To get the **Profit**, we write the following formula (*in cell F5*):

`=XLOOKUP(F3, A4:A13, C4:C13)`

## #4 – The FILTER Function

The **FILTER** function can be thought of as the new power lookup function.

Situations you will benefit from when using the **FILTER** function include:

- Cases when you need to return multiple results for one or more lookup values. When using
**VLOOKUP**or**INDEX/MATCH**, the return value is always the first occurrence of qualifying data. Suppose you need to return all instances of qualifying data? This was an extremely difficult operation to perform using older Excel functions. - Cases when you need to use a filter that automatically refreshes without the need to execute a data refresh.
- Scenarios where you need to sum or count the filtered values. The
**FILTER**function can be used as an alternative to the**SUMIF(S)**and**COUNTIF(S)**

To understand the **FILTER **function, the syntax is as follows (*parameters in brackets are optional*):

`FILTER(array, include, [if_empty])`

**array**– is the range or array you want to be included in the result. This can be the entire data set or part of the original data set.**include**– is the test you are performing on each record in the data set. This is the engine that moves the**FILTER**function forward. Here is where you define the criteria for included versus excluded records.

The logic will resemble the test portion of an**IF**function; we select a range of cells and compare each item in the range to a defined value (*ex:*) Any record that results in a**B4:B15>E4**, where column**B**is the data and cell**E4**is what the data is being compared against.**TRUE**statement will be included in the results. Any record that results in a**FALSE**statement will be excluded from the results.**[if_empty]**– defines what is to be displayed if no records match the**include**test (*ex: “No Data”*). Although this argument is optional, if it is not defined, and no data is returned from the**include**test, a**#CALC!**error will be displayed.

If we wished to generate a list of employees (*located in cells A4:A15*) who earn more than the value defined in cell

**E4**(

*90,000*), we can write the following formula.

`=FILTER(A4:B15, B4:B15 > E4)`

As a bonus, if we combine the **FILTER** function with the **SORT** function, we can have the results of the **FILTER** sorted in descending order by **Salary**.

`=SORT(FILTER(A4:B15, B4:B15 > E4), 2, -1)`

## #5 – The SEQUENCE Function

The **SEQUENCE** function is useful in the following situations:

- You need to generate a list of index numbers
- You’d like to simulate dates based on specific intervals
- Modeling and simulations
- Excel Calendar & loan amortization tables (examples in a later section)
- Transforming legacy Excel formulas to power formulas (examples in later sections)

The **SEQUENCE** function by itself is not the most exciting function. In fact, it seems downright bland and limited in its use. The power of the **SEQUENCE** function comes when it is combined with other functions. We will see many creative and impressive uses of **SEQUENCE** when we delve into the Advanced and Expert sections of this course.

To understand the **SEQUENCE **function, the syntax is as follows (*parameters in brackets are optional*):

`SEQUENCE(rows, [columns], [start], [stop])`

**rows**– is the number of rows to return.**[columns]**– is the number of columns to return. (*the default is 1*)**[start]**– is the first number in the sequence. (*the default is 1*)**[stop]**– is the amount to increment each subsequent value in the array. (*the default is 1*)

### A Simple Example

If we need to generate a list of whole numbers from 1 to 10, we can write the following formula:

`=SEQUENCE(10)`

If we wanted to make a two-column list of whole numbers ranging from 1 to 20, the formula would be as follows:

`=SEQUENCE(10, 2)`

If we wanted the same 10 by 2 list of values but we want to start with 2 and advance in increments of 3, the formula below will produce the desired results.

`=SEQUENCE(10, 2, 2, 3)`

### A Practical Example

Using the **UNIQUE** function from earlier, suppose we are generating a dynamic, unique list of **Apps** using the following function (*cell G3*).

`=UNIQUE(C3:C21)`

What we would like is a list of item numbers to the left of the unique list of **Apps** that grows and shrinks as the list of **Apps** grows and shrinks.

We can place the following **SEQUENCE** formula in cell **F3** to produce the desired results.

*NOTE: The use of the COUNTA function is to count the number of items in the spilled array starting in cell G3. This will provide the rows argument with the needed value to know how far to list numbers.*

`=SEQUENCE(COUNTA(G3#) )`

If the list of unique **Apps** were to change, the numbered list to the left will update to match in length.

### Practice Workbook

Feel free to Download the Workbook HERE.

### Leila Gharani

I'm a 5x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.