## #1 - The SORT & SORTBY Functions

### Using the SORT function

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

Around the**SORT UP**function, the syntax is as follows (*parameters in parentheses are optional*):

`SORT(array, [sort index], [sort order], [by_col])`

**reeks**– is the range or array you want to sort. This can be the entire dataset or part of the original dataset.**[sort_index]**– is the position of the column index (*read from left to right in the data*) you want to sort by. The default answer is**1**(*leftmost column*).**[sort order]**– defines the sort order for the selected column in the**[sort_index]**Enter the value for an ascending sort**1**. Enter the value for a descending sort**-1**. The default answer is**1**(*Ascending*).**[by_col]**– used when your data is not in a traditional table format, where columns are the categories (*fields*) and the rows are transactions (*defeated*). This would be used when your data is set in the opposite format; rows are categories and columns are transactions. This is an option that you will probably never or rarely use at best. Enter the value to call this argument**WHERE**. The default answer is**UNTRUE**.

If you want to sort by more than one criteria (*d.w.z., primary ascendingSort by name,secondary descendingsort by salary*), array notation is required to define the multiple "sort_index" and "sort_order" levels. Example:

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

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

This table has been converted into a real Excel table and is named "**Salary table**”.

Our goal is to sort the list in descending order by the**Annual salary**column. This can be done using the**KIND**function.

Our formula for solving this problem would be as follows:

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

In English, the logic is: "Sort the table named '**Salary table**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**SORT UP**function.

Around the**SORT UP**function, the syntax is as follows (*parameters in parentheses are optional*):

`SORT BY(array, by_array1, [sort_order1]...)`

**reeks**– is the range or array you want to sort. This can be the entire dataset or part of the original dataset.**door_array1**– is the range of items you want to sort. This column is not part of your result.**[sort_order1]**– defines the sort order for the selected column in the**[door_array1]**Enter the value for an ascending sort**1**. Enter the value for a descending sort**-1**. The default answer is**1**(*Ascending*).

Of**SORT UP**function can accept multiple "by_array" and "sort_order" declarations to allow multi-level sorting conditions (*i.e. sort by country, then sort by region, then sort by state.*)

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

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

Recommended course

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

Ready to boost your Excel skills? This course will help you master the new set of features introduced in Excel for Office 365. You'll create professional reports in a fraction of the time it used to.

Learn more

## #2 The UNIQUE feature

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

- You want to receive a unique list of items from your dataset. The result will only include each item from the dataset once in the final list. In Legacy Excel, this required using multiple functions and was quite complicated.
- You want a clear list of items from your dataset. The result shows items that appear only once in the dataset.
- You want your unique list to update automatically without having to refresh a search.

While functions exist in Excel that provide such lists, such as pivot tables, these functions require manual intervention to update if the source data changes. Because the**UNIQUE**function is dynamic, the update takes place automatically.

Around the**UNIQUE**function, the syntax is as follows (*parameters in parentheses are optional*):

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

**reeks**– is the part of the original dataset that contains your results; the part of the data from which you want to derive the list.**[by_col]**- is the direction you read the list for comparison. Choose to read the list in columns**WHERE**. Select to read the list across rows**UNTRUE**. The default selection is**UNTRUE**.**[occurs_once]**– If this is set to**WHERE**, the result list contains only items that appear once in the original data. This can be called a "separate" list. If this is set to**UNTRUE**, the result list contains all items from the original data, but only one of each item. The default selection is**UNTRUE**.

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

We have the data set below and we want to generate a unique list of it**Regio's**.

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

`=UNIQUE(TableDivRev[Region])`

But what if you have a unique list of**Division/Region**combinations? The formula would look like this:

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

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

### Generate DISTINCT lists

Let's say you just want to generate a list of**Apps**where the results are apps that appear only once in the dataset.

The following formula uses a**UNTRUE**for the**[by_col]**argument (*because we read row by row*) and use a**WHERE**for the**[exact_once]**argument.

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

## #3 - The XLOOKUP function

Of**XLSEARCH**function is the intended successor to the older, time-tested one**VLOOKUP**function.**XLSEARCH**is much more flexible and easier to use than**VLOOKUP**.

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

`=XLLOOKUP(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 should be displayed if no match exists (*e. text message or default value*)**[match_mode]**- specifies the**Agreement type**. ()**0**= Exact match {default},**-1**= exact match or next smaller,**1**= exact match or next greater,**2**= Wildcard match**[search mode]**- specifies the**Search mode**. ()**1**= First search for latest {default},**-1**= search last to first,**2**= binary search {ascending},**-2**= binary search {descending}

Our goal in the following example is to select an app from the drop-down list in the cell**F3**. From this selection we want the corresponding**Division**of column**H**and the corresponding**Gain**of column**C**.

Around the**Division**, we write the following formula (*in cel F4*):

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

### Have you seen the hidden awesomeness?

Note that in the data, the column from which we return data is to the** LINKS**of the column we are looking for.

This would be impossible with a traditional one**VLOOKUP**function (*without doing some crazy in-memory, virtual table construction that only 9 people on planet Earth like.*)

We've also added an argument that returns a text response if the item being searched for isn't found in the list.

Around the**Gain**, we write the following formula (*in cel F5*):

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

## #4 - The FILTER function

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

Situations that you take advantage of when using the**FILTER**Feature includes:

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

Around the**FILTER**function, the syntax is as follows (*parameters in parentheses are optional*):

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

**reeks**– is the range or array you want to include in the result. This can be the entire dataset or part of the original dataset.**Involving**– is the test you perform on each record in the dataset. This is the motor that moves the motor**FILTER**function forward. Here you define the criteria for included versus excluded records.

The logic will resemble the test portion of one**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**E 4**with which the data is compared.**WHERE**statement will be included in the results. Any record that results in a**UNTRUE**statement is excluded from the results.**[if_empty]**– defines what to display if no records match the**Involving**test (*e.g.: "No data"*). Although this argument is optional, if it is not defined and no data is returned by it**Involving**testing, a**#CALC!**error is displayed.

If we want to generate a list of employees (*are in cells A4:A15*) that earn more than the value defined in the cell

**E 4**(

*90.000*), we can write the following formula.

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

As a bonus, if we use the**FILTER**function with the**KIND**function, we can get the results of the**FILTER**sorted descending by**Salary**.

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

## #5 - The SEQUENCE function

Of**REEKS**function is useful in the following situations:

- You must generate a list of index numbers
- You want to simulate dates based on specific intervals
- Modeling and simulations
- Excel calendar and loan amortization tables (examples in a later section)
- Transform legacy Excel formulas into powerful formulas (examples in later sections)

Of**REEKS**feature in itself is not the most exciting feature. In fact, it seems downright boring and limited in its uses. The power of the**REEKS**feature comes when it is combined with other features. We will see many creative and impressive uses of it**REEKS**when we get into the advanced and expert sections of this course.

Around the**REEKS**function, the syntax is as follows (*parameters in parentheses are optional*):

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

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

### A simple example

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

`=REEKS(10)`

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

`=REEKS(10, 2)`

If we want the same list of values from 10 by 2, but we want to start with 2 and go up in steps of 3, the formula below will produce the desired results.

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

### A practical example

The habits**UNIQUE**function from before, say we generate a dynamic unique list of**Apps**using the following function (*THE 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 post the following**REEKS**formulas in the cel**F3**to produce the desired results.

*NOTE: Using the NUMBERfunction is to count the number of items in spilled array starting in cellG3. This will therowsargument with the necessary value to know how far to display numbers.*

`=REEKS(AANTALA(G3#) )`

If the list of unique**Apps**should change, the numbered list on the left will be updated to match length.

### Practice workbook

Feel free to download the workbookHERE.

Published on: January 7, 2021

Last modified: March 11, 2023

Categories:excel,Formulas

Tagged as:Dynamic arrays,FILTER function,New functions,Office 365,REEKS,SORT function,SORT UP,UNIQUE,XLSEARCH

### Leila Gharani

I am a 5x Microsoft MVP with over 15 years of experience implementing and professionalizing management information systems of various sizes and types.

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 various topics.

More about Leila Join over 300,000 professionals in our courses