List of Dynamic Array Formulas in Excel (7 Practical Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I am going to share with you 7 practical list of dynamic array formulas in Excel. You can easily apply these functions to perform a wide range of calculations including dynamic array generation inside an Excel worksheet. To achieve this task, we will also see some useful features that might come in handy in many other Excel-related tasks.


How to Create List of Dynamic Array with Formulas in Excel: 7 Easy Examples

1. The UNIQUE Function

Function Objective

The UNIQUE function in Excel is a function that returns a list of unique values in a range or array. It is often used in combination with other functions, such as the COUNTIF function, to create a list of dynamic array formulas in Excel and to count the number of unique values in a range.

Syntax

UNIQUE(array, [by_col], [exactly_once])

Arguments Explanation

Argument Required/Optional Explanation
array Required Firstly, this is the range or array from which you want to extract unique values.
by_col Optional When set to TRUE, the function will extract unique values from each column in the range and return them as a vertical array. Otherwise, if set to FALSE (the default), the function will extract unique values from each row in the range and return them as a horizontal array.
exactly_once Optional When set to TRUE, the function will only return values that appear exactly once in the range. Conversely, if set to FALSE (the default), the function will return all unique values in the range, regardless of how many times they appear. Essentially, setting the “unique” parameter to TRUE will filter out any duplicate values in the range, while setting it to FALSE will include all values, even if they appear multiple times. Also, this can be useful for identifying and removing duplicates in a dataset or for highlighting unique values.

Return Parameter

The return value of the UNIQUE function is an array of unique values from the specified range or array.

Use of UNIQUE Function

list of dynamic array formulas in excel


2. The FILTER Function

Function Objective

The FILTER function in Excel is a function that allows you to filter a range of data based on specified criteria and return a new set of data that meets those criteria. Additionally, this is the second function in the list of dynamic array formulas in Excel. Furthermore, it is similar to the “Filter” feature in the Data tab, but it allows you to filter data using a formula, rather than manually selecting the criteria.

Syntax

FILTER(range, condition1, [condition2], ...)

Arguments Explanation

Argument Required/Optional Explanation
range Required Firstly, this is the range of cells that you want to filter.
condition1, condition2, etc Required Similarly, these are the criteria that you want to use to filter the data. Specifically, each condition is a logical test that returns either TRUE or FALSE. As a result, the function will only return cells from the range that meet all of the specified criteria.

Return Parameter

The FILTER function returns data that meets specific criteria, which can be a single cell, a range of cells, or an array of values, depending on the size and shape of the original data and the criteria being used. Additionally, the type and dimensions of the returned data will vary based on the original data and the criteria being used. Moreover, if the original data is small and the criteria are narrow, the returned data may be a single cell, but if the original data is large and the criteria are broader, the returned data may be a range of cells or an array of values.

Use of FILTER Function

filter function as list of dynamic array formulas in Excel


3. The RANDARRAY Function

Function Objective

The RANDARRAY function in Excel is a function that generates a random array of numbers between a specified range. Specifically, it can be used to create a set of random numbers for use in statistical analysis or for other purposes.

Syntax

RANDARRAY([rows], [columns], [min], [max])

Arguments Explanation

Argument Required/Optional Explanation
rows Optional Firstly, the number of rows in the array is an optional parameter in the FILTER function. If it is not specified, the default value is 1. In other words, if you do not specify the number of rows in the array, the function will assume that there is only 1 row. Also, this parameter can be useful for controlling the size and shape of the returned data, particularly if you are using the FILTER function to return an array of values. By specifying the number of rows, you can ensure that the returned data is the size and shape that you need for your specific use case.
columns Optional Similarly, the number of columns in the array is also an optional parameter in the FILTER function. As long as it is not specified, the default value is 1.
min Optional The minimum value for the random numbers. When not specified, the default is 0.
max Optional In the same way, it is the maximum value for the random numbers. Whenever not specified, the default is 1.

Return Parameter

The return value of the RANDARRAY function is an array of random numbers between the specified range. Besides, the size and shape of the array will depend on the values of the rows and columns arguments.

Use of RANDARRAY Function

randarray function from list of dynamic array formulas in Excel


4. The SEQUENCE Function

Function Objective

The SEQUENCE function in Excel is a function that generates a sequential list of numbers, with a specified step size and starting value. Specifically, it can be used to create a series of numbers for use in statistical analysis or for other purposes.

Syntax

SEQUENCE(rows, [columns], [start], [step])

Arguments Explanation

Argument Required/Optional Explanation
rows Required Firstly, it is the number of rows in the array.
columns Optional The number of columns in the array. When not specified, the default is 1.
start Optional The starting value for the sequence. Whenever not specified, the default is 1.
step Optional The step size for the sequence. In the event that is not specified, the default is 1.

Return Parameter

The return value of the SEQUENCE function is an array of numbers that form a sequential series, starting at the specified start value and increasing by the specified step value. In addition, the size and shape of the array will depend on the values of the rows and columns arguments.

Use of SEQUENCE Function


5. The SORT Function

Function Objective

The SORT function in Excel is a function that allows you to sort a range of data based on specified criteria. Additionally, it is similar to the Sort feature in the Data tab, but it also allows you to sort data using a formula, rather than manually selecting the criteria.

Syntax

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

Arguments Explanation

Argument Required/Optional Explanation
array Required This is the range of cells that you want to sort.
sort_index Optional This is the index of the column or row that you want to sort the data by. As long as not specified, the function will sort the data by the first column or row.
sort_order Optional This is the order in which you want to sort the data. Now, if this is set to 1 (ascending), the function will sort the data from lowest to highest. In the case that is set to -1 (descending), the function will sort the data from highest to lowest. Also, when not specified, the default is 1 (ascending).
by_col Optional If and when set to TRUE, the function will sort the data by column. Otherwise if set to FALSE (the default), the function will sort the data by row.

Return Parameter

The return value of the SORT function is a new set of data that has been sorted according to the specified criteria. The returned data will have the same size and shape as the original data.

Use of SORT Function


6. The SORTBY Function

Function Objective

The SORTBY function in Excel is a function that allows you to sort a range of data based on the values in another range or array. It is similar to the “Sort” feature in the Data tab, but it allows you to sort data using a formula, rather than manually selecting the criteria.

Syntax

SORTBY(array, by_array, [sort_order], [by_col])

Arguments Explanation

Argument Required/Optional Explanation
array Required This is the range of cells that you want to sort.
by_array Required This is the range or array of values that you want to use to sort the data. Specifically, it must have the same size and shape as the array argument.
sort_order Optional This is the order in which you want to sort the data. If set to 1 (ascending), the function will sort the data from lowest to highest. Else when set to -1 (descending), the function will sort the data from highest to lowest. Whenever not specified, the default is 1 (ascending).
by_col Optional Provided that it is set to TRUE, the function will sort the data by column. Else when set to FALSE (the default), the function will sort the data by row.

Return Parameter

The return value of the SORTBY function is a new set of data that has been sorted according to the values in the by_array argument. Additionally, the returned data will have the same size and shape as the original data.

Use of SORTBY Function


7. The XLOOKUP Function

Function Objective

The XLOOKUP function in Excel is a function that searches for a value in a range or array and returns a corresponding value from a specified column or row. Additionally, it is similar to the VLOOKUP function, but it has additional features and options that make it more flexible and powerful.

Syntax

XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])

Arguments Explanation

Argument Required/Optional Explanation
lookup_value Required This is the value that you want to search for in the lookup_array.
lookup_array Required This is the range or array of cells that you want to search for the lookup_value.
return_array Optional Similarly, this is the range or array of cells that contain the values that you want to return. It must have the same size and shape as the lookup_array.
match_mode Optional This is a number that specifies how the function should match the lookup_value to the values in the lookup_array. When set to 0 (the default), the function will use an exact match. Else whenever set to -1, the function will use the nearest smaller value. In the case that it is set to 1, the function will use the nearest larger value.
search_mode Optional This is a number that specifies how the function should search the lookup_array for the lookup_value. When set to 1 (the default), the function will search the array in ascending order. Else whenever set to -1, the function will search the array in descending order. If and when set to 2, the function will use a binary search algorithm to search the array.

Return Parameter

The return value of the XLOOKUP function is the value in the return_array that corresponds to the lookup_value in the lookup_array.

Use of XLOOKUP Function


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on the 7 practical lists of dynamic array formulas in Excel. As you can see, there are quite a few ways to use these functions. So wisely choose the function that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please let me know in the comments.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel projects.
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo