How to Use Excel XLOOKUP, FILTER, SORT, UNIQUE, SORTBY, and SEQUENCE Functions (2023)

#1 - The SORT & SORTBY Functions

Using the SORT function

OfKINDThe function allows you to dynamically sort the results of your dataset based on a column within the data.

Around theSORT UPfunction, 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 is1(leftmost column).
• [sort order]– defines the sort order for the selected column in the[sort_index]Enter the value for an ascending sort1. Enter the value for a descending sort-1. The default answer is1(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 argumentWHERE. The default answer isUNTRUE.

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 theAnnual salarycolumn. This can be done using theKINDfunction.

Our formula for solving this problem would be as follows:

=SORT(TableSalary, 3, -1)

In English, the logic is: "Sort the table named 'Salary tableby 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 theSORT UPfunction.

Around theSORT UPfunction, 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 sort1. Enter the value for a descending sort-1. The default answer is1(Ascending).

OfSORT UPfunction 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)

#2 The UNIQUE feature

OfUNIQUEfunction 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 theUNIQUEfunction is dynamic, the update takes place automatically.

Around theUNIQUEfunction, 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 columnsWHERE. Select to read the list across rowsUNTRUE. The default selection isUNTRUE.
• [occurs_once]– If this is set toWHERE, the result list contains only items that appear once in the original data. This can be called a "separate" list. If this is set toUNTRUE, the result list contains all items from the original data, but only one of each item. The default selection isUNTRUE.

Let's look at some examples of theUNIQUEfunction in action.

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

We can write the followingUNIQUEformula:

=UNIQUE(TableDivRev[Region])

But what if you have a unique list ofDivision/Regioncombinations? 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 ofAppswhere the results are apps that appear only once in the dataset.

The following formula uses aUNTRUEfor the[by_col]argument (because we read row by row) and use aWHEREfor the[exact_once]argument.

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

#3 - The XLOOKUP function

OfXLSEARCHfunction is the intended successor to the older, time-tested oneVLOOKUPfunction.XLSEARCHis much more flexible and easier to use thanVLOOKUP.

The logic for theXLSEARCHfunction 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 theAgreement type. (0= Exact match {default},-1= exact match or next smaller,1= exact match or next greater,2= Wildcard match)
• [search mode]- specifies theSearch 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 cellF3. From this selection we want the correspondingDivisionof columnHand the correspondingGainof columnC.

Around theDivision, we write the following formula (in celF4):

=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 theLINKSof the column we are looking for.

This would be impossible with a traditional oneVLOOKUPfunction (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 theGain, we write the following formula (in celF5):

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

#4 - The FILTER function

OfFILTERfunction can be thought of as the new power lookup function.

Situations that you take advantage of when using theFILTERFeature includes:

• Cases where you need to return multiple results for one or more lookup values. When usingVLOOKUPofINDEX/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. TheFILTERfunction can be used as an alternative to theSUMIF(S)InCOUNTIF(S)

Around theFILTERfunction, 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 motorFILTERfunction forward. Here you define the criteria for included versus excluded records.
  The logic will resemble the test portion of oneIFfunction; we select a range of cells and compare each item in the range to a defined value (ex:B4:B15>E4, where columnBis the data and cellE 4with which the data is compared.) Any record that results in aWHEREstatement will be included in the results. Any record that results in aUNTRUEstatement is excluded from the results.
• [if_empty]– defines what to display if no records match theInvolvingtest (e.g.: "No data"). Although this argument is optional, if it is not defined and no data is returned by itInvolvingtesting, a#CALC!error is displayed.

If we want to generate a list of employees (are in cellsA4:A15) that earn more than the value defined in the cellE 4(90.000), we can write the following formula.

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

As a bonus, if we use theFILTERfunction with theKINDfunction, we can get the results of theFILTERsorted descending bySalary.

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

#5 - The SEQUENCE function

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

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

Around theREEKSfunction, 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 habitsUNIQUEfunction from before, say we generate a dynamic unique list ofAppsusing the following function (THEG3).

=UNIQUE(C3:C21)

What we would like is a list of item numbers to the left of the unique list ofAppsthat grows and shrinks as the list ofAppsgrows and shrinks.

We can post the followingREEKSformulas in the celF3to produce the desired results.

NOTE: Using theNUMBERfunction 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 uniqueAppsshould change, the numbered list on the left will be updated to match length.

Practice workbook

Feel free to download the workbookHERE.