Excel Dynamic Array: 8 Must-Know Formulas

This Excel Dynamic Array tutorial is suitable for users of Excel for Microsoft 365.  

Objective 

Explore Microsoft’s NEW Excel Dynamic Array Functions and use them effectively to solve problems.  

This guide covers:

Excel Dynamic Array Explained 

Dealing with an Excel array is quite complicated in itself, don’t you think? 

What if there is a way to handle an Excel array easily,  and get your desired results without any hassle?  

Let’s say, you are managing your school records and you want to search only for the marks of students from the east block and have taken only the English exam. 

Is there any direct Excel array formula or method that can accomplish this without making life difficult?

Enter Excel Dynamic array formulas. It does many complicated things the easy way. We at Simonsezit simply love it. 

Related: 

Easily Make a Bullet Chart in Excel—1 Bonus Video Included

Bar Graph in Excel — All 4 Types Explained Easily (Excel Sheet Included)

How to Easily Create an Excel Histogram?—2 Best Methods

Excel Dynamic Array Formulas were introduced by Microsoft in 2018 and are the most significant change to Excel formulas in years. Dynamic Arrays allow you to work with multiple values at the same time in a formula.

This feature enables Excel formulas to return numerous results based on a single formula entered in one cell. The results are also dynamic, meaning that if the source data changes, the results will dynamically update.  

The process of returning multiple values from one Excel array formula is referred to as ‘spilling’ because the results appear to spill across the worksheet. 

This makes this new Excel Dynamic Array functions very powerful and flexible with various possible applications. They are definitely a very important addition to the Excel formulas toolbox.

If you are worried that these Excel dynamic array functions might be very complicated and hard to implement in your project, don’t fret. We are here to help. 

In this guide, we have elucidated all the 8 Excel formulas related to Excel dynamic array with examples for your convenience. So grab your coffee, crack your knuckles, and get ready to sharpen your Excel skills. 

VIDEO TUTORIAL – Excel Dynamic Array Functions

8 Must Know Excel Dynamic array Functions 

Microsoft has added these  8 new Excel formulas that use dynamic arrays to solve problems.

Function	Purpose
1. FILTER	Filter data and return matching records
2. RANDARRAY	Generate an array of random numbers
3. SEQUENCE	Generate an array of sequential numbers
4. SORT	Sort range by column
5. SORTBY	Sort range by another range or array
6. UNIQUE	Extract unique values from a list or range
7. XLOOKUP	Modern replacement for VLOOKUP
8. XMATCH	Modern replacement for the MATCH function

In this guide, we are going to look at an example of each Excel dynamic array function along with their respective syntaxes. We’ll also see how each Excel array formula behaves with and without optional arguments. 

FILTER 

The  FILTER is an Excel array formula that filters data based on criteria that you specify. It returns all matching records, and if it cannot find any records, it will return a value that you specify in the formula.  

=FILTER (array, include, [if_empty]) 

In the example below, Excel uses the FILTER function to look in the Excel array B4:E23 for the ‘Block’ cell range and pull out the value located in cell G4 (East).

This is our first criteria. It then looks in the ‘Exam’ cell range and pulls out the value located in G7 (English). The two quotation marks tell Excel to display nothing if it cannot find any matches.  

This is the result: 

RANDARRAY 

The RANDARRAY function generates random numbers. The size of the Excel array is specified by the rows and columns argument.

You can also select the range in which the number will fall and if you want them to be decimals or integers (whole numbers). It is like a more powerful version of the RANDBETWEEN function.  

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

In the example below, Excel has used the RANDARRAY function to output a table of random decimal numbers, eight rows high, and four columns wide.  

Now let us look at an example that utilizes the optional arguments.  

In this example, Excel has used the RANDARRAY function to output a table of random numbers six rows high, six columns wide, with values between 10 and 50. The TRUE argument tells Excel to output integer (whole) numbers as opposed to decimals.  

Also Read:

Excel Quick Analysis Tool – The Best Guide (5 Examples)

How to Lock Cells in Excel?— 4 Best Methods with Examples

How To Find Duplicates in Excel? (3 Easy Methods)

SEQUENCE 

The SEQUENCE function generates a list of sequential numbers. The Excel array can be one-dimensional or two-dimensional, and you can specify the start value and the step value. If only the rows argument is defined, Excel will output a list of values starting at one by default.  

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

In this example, Excel has used the SEQUENCE function with just the rows argument to output a list that starts at one.  

In this example, we are adding a value for the optional column’s argument. The result is a list of sequential values seven rows high and two columns wide, starting at one.  

In this example, Excel has used the SEQUENCE function to output a list of sequential numbers ten rows high, eight columns wide, starting at ten and increasing in steps of five.  

SORT 

The SORT function in Excel sorts the contents of a range using one or more columns.  

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

By default, the SORT function will sort values in ascending order using the first column. You can utilize the optional arguments to control which column to sort by and in what order.  

In this example, Excel has used the SORT function to sort Marks in the left-hand table in descending order and output the results to the table on the right.  

The SORT looks in the array B4:E23 for the 4th column and sorts the results in descending order.  

If no [sort_order] argument is specified, the default sort is in ascending order.  

SORTBY 

The Excel SORTBY function sorts the contents of a range based on the values from another range. You can do multiple sorts on the same data.  

=SORTBY (array, by_array, [sort_order], [array/order], …) 

In this example, Excel uses the SORTBY function to sort the information in the table on the left by the Mark in descending order. It outputs the results to the table on the right.  

In this example, Excel uses the SORTBY function to sort the information in the table on the left-hand side by the Mark in descending order and then by the Student Name in ascending order.  

UNIQUE 

The UNIQUE function returns a list of unique values from a range.  

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

In this example, Excel uses the UNIQUE function to output a list of unique values in a specified range.  

In this example, Excel uses the UNIQUE function to return a list of all unique items in a horizontal range.  

XLOOKUP 

The XLOOKUP function is a modern replacement for functions like VLOOKUP, INDEX, and MATCH. It can lookup in vertical and horizontal ranges and supports approximate and exact matches.  

=XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode]) 

In the example below, Excel has used the XLOOKUP function to lookup the App listed in cell H4 in the App cell range and return the corresponding result from the Revenue column.  

XMATCH 

The XMATCH function performs a lookup and returns a position in vertical or horizontal ranges. It is the latest iteration of the MATCH function and supports approximate and exact matching. 

=XMATCH (lookup_value, lookup_array, [match_mode], [search_mode]) 

In this example, Excel uses the XMATCH function to look up the value in cell F3, in the App named range. It is doing an exact match for the word ‘Slack,’ and it starts the lookup from the first item in the list.   

Let’s Conclude

We are nearing the end of this guide on Excel Dynamic array formulas.  Let’s wrap this up with the key takeaway of Dynamic arrays in Excel. 

Dynamic arrays are used whenever we need to dynamically derive multiple values from an array of data using a single formula. Additionally, Dynamic Arrays in Excel are a great addition to the Excel formulas catalogue.

We’ll cover more similarly complex Excel formulas with interesting use-cases in our upcoming guides in great detail. 

Learn more about dynamic arrays and other advanced Excel formulas from our Excel courses

Suggested Reads: 

Sumif With Multiple Columns – The Sumifs Function In Excel

The Best Excel Project Management Template in 2021

How to Use Excel COUNTIFS: The Best Guide

FAQS

 

For more Free Excel tutorials from Simon Sez IT. Take a look at our Excel Resource Center.

Other Excel classes you might like:

• Protecting WorkBooks in Excel
• Logical Functions in Excel
• Designing Better Spreadsheets in Excel
• Introduction to Power Pivot & Power Query in Excel

To learn Excel with Simon Sez IT. Take a look at the Excel courses available.