## #1 – SORT AND SORT BY functions

### Using the SORT function

He**PUZZLE OUT**The function allows you to dynamically sort the data results based on a column in the data.

Grip**ORDER BY**function, the syntax is (*Parameters in parentheses are optional*):

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

**education**– is the range or table you want to sort. It can be the full data set or part of the original data set.**[sort_index]**– is the position of the column index (*reading data from left to right*) by which you want to sort. The default answer is**1**(*left column*).**[Sort order]**– sets the sort order for the selected column in the file**[sort_index]**For ascending sort, enter a value**1**. For descending order, enter a value**-1**. The default answer is**1**(*increasing*).**[by_col]**– used when the data is not in the traditional table format, where the columns are categories (*with*) and rows are transactions (*documentation*). This will be used when your data is set to reverse format. rows are categories and columns are transactions. This is an option you will probably never or rarely use. To call this argument, enter a value**TRUE**. The default answer is**LIE**.

If you want to sort by more than one criteria (*tj., primary growthsort by name,descending secondarysort by salary*), array notation will be required to specify multiple "sort_index" and "sort_order" levels. Example:

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

For example, let's take this dataset of names, dates, and salaries.

This table has been converted to a proper Excel table and named "**Salary Table**".

Our goal is to order the list in descending order**Annual salary**column. This can be done using**PUZZLE OUT**mode.

Our formula to solve this problem would be:

`=SORT(Table salary, 3, -1)`

In English the logic is "Sort by table name"**salary table"**through the third column (**3**) in descending order**(-1**)»

### Using the SORT BY function

If you want to sort the data by a column that will not appear in the result, you can use the method**ORDER BY**mode.

Grip**ORDER BY**function, the syntax is (*Parameters in parentheses are optional*):

`SORT BY (table, by_table1, [sort_sort1]…)`

**education**– is the range or table you want to sort. It can be the full data set or part of the original data set.**by_array1**– is an array of items you want to sort. This column will not be part of your score.**[order_sort1]**– sets the sort order for the selected column in the file**[by_matrix1]**For ascending sort, enter a value**1**. For descending order, enter a value**-1**. The default answer is**1**(*increasing*).

He**ORDER BY**The 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 dataset as before, if we want to create a list of names sorted in ascending order, but we don't want adjacent columns of data, we can write the following formula.

`=SORT BY (table salary[name], table salary[yearly salary], -1)`

marked course

## Master the NEW Excel features in Office 365 and Office 2021

Ready to improve your Excel skills? This course will help you master the new feature set introduced in Excel for Office 365. Create professional reports in a fraction of the time it took before.

learn more

## #2 UNIQUE feature

He**SINGLE**The function is useful in the following cases:

- You want to get a unique list of items from your dataset. The result will include each item in the dataset only once in the final list. In previous versions of Excel, this required a lot of features and was quite complex.
- You want an explicit list of items in your dataset. The result will show items that appear only once in the dataset.
- You want your unique list to update automatically without you having to update the query.

While there are functions in Excel that provide such lists, such as pivot tables, they require manual intervention to update when the underlying data changes. Because**SINGLE**operation is dynamic, update is automatic.

Grip**SINGLE**function, the syntax is (*Parameters in parentheses are optional*):

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

**education**– is part of the original data set that contains its results; part of the data from which you want to create a list.**[by_col]**– is the address at which you read the list to compare. To read the list in columns, select**TRUE**. To read the list in rows, select**LIE**. The default option is**LIE**.**[happens_once]**– If set to**TRUE**, the result list contains only the items that appear once in the original data. This can be called a "clear" list. If set to**LIE**, the result list contains all elements of the original data, but only one of each element. The default option is**LIE**.

Let's look at some examples**SINGLE**function in action.

We have the following data set and want to create a unique list**Regions**.

We can write the following**SINGLE**type:

`=UNIQUE(TableDivRev[Region])`

But what if you want a unique list?**Address/Region**combinations; The formula will look like this:

`=UNIQUE(TableDivRev[ [division] : [region] ] )`

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

### Create DIFFERENT lists

Let's say you just want to create a list**Applications**where results are applications that appear only once in the dataset.

The formula below will use a**LIE**For**[by_col]**dispute (*because we read in verses*) and use A**TRUE**For**[exactly once]**disagreement.

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

## #3 – XLOOKUP function

He**XSEARCH**it is the intended successor of the previous one, demonstrably**VERTICAL SEARCH**mode.**XSEARCH**is much more flexible and easy to use than**VERTICAL SEARCH**.

logic for**XSEARCH**the function is as follows:

`=XLOOKUP(lookup_value, lookup_array, return_array)`

**lookup value**– is the value we want to find**search array**– is a list where you can find the value you are looking for**matrix_return**– is a list you can return to after discovery**[if_not_found]**– what should be displayed if there is no match (*my. text message or default*)**[match_mode]**– he clarifies**Match type**. ()**0**= exact match {default},**-1**= Exact or next minor match,**1**= Exact Match or Next Higher,**2**= wild card match**[search function]**– he clarifies**search function**. ()**1**= Search from beginning to end {default},**-1**= Search last to first,**2**= binary search {ascending},**-2**= binary search {descending}

Our goal in the example below is to select an application from the dropdown list located in a cell**F3**. From this selection we want to draw relevant**Department**of the column**H**and bound**Income**of the column**do**.

Reach**Department**, we write the following formula (*in the cell F4*):

`=BUSCARX(F3;I4:I13;H4:H13"Missing")`

### Did you see the hidden glory?

Note that in the data, the column we are returning data from is inside** LEWY**column we are looking for.

In the traditional case, this would be impossible.**VERTICAL SEARCH**mode (*without building some crazy virtual table in memory that only 9 people on Earth find enjoyable.*)

We also include an argument that sends a text response if the item you're looking for is not in the list.

Reach**Income**, we write the following formula (*in the cell F5*):

`=BUSCARX(F3; A4:A13; C4:C13)`

## #4 – FILTER mode

He**FILTER**can be seen as a new power-seeking mode.

Situations in which you will benefit from use**FILTER**function includes:

- Cases where you need to return multiple results for one or more search values. during use
**VERTICAL SEARCH**lubricant**INDEX/POSITION**, the return value is always the first occurrence of the grade data. Suppose you need to return all suitable data instances. This was an extremely difficult operation with Excel's legacy capabilities. - Cases where you need to use a filter that updates automatically without having to refresh data.
- Scenarios where filtered values need to be summed or counted. He
**FILTER**The function can be used as an alternative**SUMIF(Y)**I**COUNTIF(S)**

Grip**FILTER**function, the syntax is (*Parameters in parentheses are optional*):

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

**education**– is the range or array you want to include in the result. It can be the full data set or part of the original data set.**light**– is a test you run on every record in the dataset. This is the engine that drives it**FILTER**forward. This is where you define the criteria for which records are included and excluded.

The logic will look like the test of a**AND**mode; we select a range of cells and compare each element of the range with a specified value (*it was:*) Any entry leading to a**B4:B15>E4**where column**si**it's data and mobile phone**mi 4**is what the data is compared to.**TRUE**the statement will be included in the results. Each entry leading to a**LIE**the statement will be excluded from the results.**[if_empty]**– Specifies what to display if no file matches**light**evidence (*for example, "No data"*). Although this argument is optional, if it is not set and no data is returned from**light**crisis, a**#CALC!**an error will appear.

If we wanted to create a list of employees (*found in cells A4:A15*) that earn more than the value set in the cell

**mi 4**(

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

`=FILTRO(A4:B15; B4:B15 > E4)`

As a bonus if we combine**FILTER**z-function**PUZZLE OUT**functions, we can get the results**FILTER**sorted in descending order by**Payment**.

`=CLASIFICAR(FILTRO(A4:B15; B4:B15 > E4); 2; -1)`

## #5 - SEQUENCE function

He**SEQUENCE**The function is useful in the following cases:

- You need to create a list of index numbers
- You want to simulate dates based on specific intervals
- Modeling and simulations
- Excel calendar and loan repayment tables (examples below)
- Convert legacy Excel formula to complex formulas (examples in next sections)

He**SEQUENCE**the feature itself is not the coolest feature. In fact, it seems completely bland and of limited use. Power**SEQUENCE**a function appears when combined with other functions. We will see many creative and impressive applications.**SEQUENCE**as we delve into the advanced and special sections of this course.

Grip**SEQUENCE**function, the syntax is (*Parameters in parentheses are optional*):

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

**scandal**– is the number of rows to return.**[columns]**– is the number of columns to return. (*The default value is 1*)**[principle]**– is the first number in the series. (*The default value is 1*)**[arrest]**– is the incremental amount of each successive value in the array. (*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:

`=SEQUENCE(10)`

If we wanted to create a two-column list of integers between 1 and 20, the formula would look like this:

`=SEQUENCE(10, 2)`

If we wanted the same 10 by 2 list of values, but wanted to start with 2 and increment by 3, the following formula would give the desired results.

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

### A practical example

using**SINGLE**from the above function, suppose we create a dynamic unique list**Applications**using the following function (*it was whole G3*).

`=UNIQUE(C3:C21)`

We would like a list of item numbers to the left of the individual list**Applications**which grows and shrinks with the list**Applications**grows and shrinks.

We can put the following**SEQUENCE**type in the cell**F3**to get the desired results.

*NOTE: Use NUMBERthe function is to count the number of elements in a spilled array starting from a cellG3. will providescandalargument with the value you need to know how far to display the numbers.*

`=SEQUENCE(COUNT(G3#) )`

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

### workbook

We invite you to download the WorkbookHERE.

Published: January 7, 2021

Last modified: March 11, 2023

Category:I pay,formulas

Tagged with:dynamic arrays,FILTER,new features,Office 365,SEQUENCE,SORT function,ORDER BY,SINGLE,XSEARCH

### Leila Garani

I am a 5-time Microsoft MVP with over 15 years of experience specializing in the implementation and management of IT systems of various sizes and natures.

I am a teacher in the economics of education, an economist, a consultant, an expert in the Oracle HFM accounting system, a SAP BW project manager. My passion is to teach, experiment and share. I am also addicted to learning and enjoy taking online courses on various subjects.

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