How to use XLOOKUP, FILTER, SORT, UNIQUE, SORT and SEQUENCE functions in Excel (2023)

#1 – SORT AND SORT BY functions

Using the SORT function

HePUZZLE OUTThe function allows you to dynamically sort the data results based on a column in the data.

GripORDER BYfunction, 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 is1(left column).
  • [Sort order]– sets the sort order for the selected column in the file[sort_index]For ascending sort, enter a value1. For descending order, enter a value-1. The default answer is1(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 valueTRUE. The default answer isLIE.

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 orderAnnual salarycolumn. This can be done usingPUZZLE OUTmode.

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 methodORDER BYmode.

GripORDER BYfunction, 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 value1. For descending order, enter a value-1. The default answer is1(increasing).

HeORDER BYThe 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

How to use XLOOKUP, FILTER, SORT, UNIQUE, SORT and SEQUENCE functions in Excel (4)

#2 UNIQUE feature

HeSINGLEThe 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. BecauseSINGLEoperation is dynamic, update is automatic.

GripSINGLEfunction, 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, selectTRUE. To read the list in rows, selectLIE. The default option isLIE.
  • [happens_once]– If set toTRUE, the result list contains only the items that appear once in the original data. This can be called a "clear" list. If set toLIE, the result list contains all elements of the original data, but only one of each element. The default option isLIE.

Let's look at some examplesSINGLEfunction in action.

We have the following data set and want to create a unique listRegions.

We can write the followingSINGLEtype:

=UNIQUE(TableDivRev[Region])

But what if you want a unique list?Address/Regioncombinations; 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 listApplicationswhere results are applications that appear only once in the dataset.

The formula below will use aLIEFor[by_col]dispute (because we read in verses) and use ATRUEFor[exactly once]disagreement.

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

#3 – XLOOKUP function

HeXSEARCHit is the intended successor of the previous one, demonstrablyVERTICAL SEARCHmode.XSEARCHis much more flexible and easy to use thanVERTICAL SEARCH.

logic forXSEARCHthe 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 clarifiesMatch type. (0= exact match {default},-1= Exact or next minor match,1= Exact Match or Next Higher,2= wild card match)
  • [search function]– he clarifiessearch 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 cellF3. From this selection we want to draw relevantDepartmentof the columnHand boundIncomeof the columndo.

ReachDepartment, we write the following formula (in the cellF4):

=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 insideLEWYcolumn we are looking for.

In the traditional case, this would be impossible.VERTICAL SEARCHmode (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.

ReachIncome, we write the following formula (in the cellF5):

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

#4 – FILTER mode

HeFILTERcan be seen as a new power-seeking mode.

Situations in which you will benefit from useFILTERfunction includes:

  • Cases where you need to return multiple results for one or more search values. during useVERTICAL SEARCHlubricantINDEX/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. HeFILTERThe function can be used as an alternativeSUMIF(Y)ICOUNTIF(S)

GripFILTERfunction, 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 itFILTERforward. This is where you define the criteria for which records are included and excluded.
    The logic will look like the test of aANDmode; we select a range of cells and compare each element of the range with a specified value (it was:B4:B15>E4where columnsiit's data and mobile phonemi 4is what the data is compared to.) Any entry leading to aTRUEthe statement will be included in the results. Each entry leading to aLIEthe statement will be excluded from the results.
  • [if_empty]– Specifies what to display if no file matcheslightevidence (for example, "No data"). Although this argument is optional, if it is not set and no data is returned fromlightcrisis, a#CALC!an error will appear.

If we wanted to create a list of employees (found in cellsA4:A15) that earn more than the value set in the cellmi 4(90 000), we can write the following formula.

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

As a bonus if we combineFILTERz-functionPUZZLE OUTfunctions, we can get the resultsFILTERsorted in descending order byPayment.

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

#5 - SEQUENCE function

HeSEQUENCEThe 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)

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

GripSEQUENCEfunction, 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

usingSINGLEfrom the above function, suppose we create a dynamic unique listApplicationsusing the following function (it was wholeG3).

=UNIQUE(C3:C21)

We would like a list of item numbers to the left of the individual listApplicationswhich grows and shrinks with the listApplicationsgrows and shrinks.

We can put the followingSEQUENCEtype in the cellF3to get the desired results.

NOTE: UseNUMBERthe 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 uniqueApplicationswill 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

References

Top Articles
Latest Posts
Article information

Author: Delena Feil

Last Updated: 04/19/2023

Views: 6128

Rating: 4.4 / 5 (65 voted)

Reviews: 88% of readers found this page helpful

Author information

Name: Delena Feil

Birthday: 1998-08-29

Address: 747 Lubowitz Run, Sidmouth, HI 90646-5543

Phone: +99513241752844

Job: Design Supervisor

Hobby: Digital arts, Lacemaking, Air sports, Running, Scouting, Shooting, Puzzles

Introduction: My name is Delena Feil, I am a clean, splendid, calm, fancy, jolly, bright, faithful person who loves writing and wants to share my knowledge and understanding with you.