Dynamic array functions are a set of powerful formula tools introduced with the release of **Excel 365 **and **Excel 2021 **for **Windows **and **Mac**. These functions are like shortcuts that can automatically fill several cells with results without the need for complex formulas or manual adjustments.

We’ll use the sample dataset below to show how these functions work.

## Download Practice Workbook

### Function 1 – Using UNIQUE Function

**The UNIQUE Function** in Excel is used to get the unique values from a list of tables. We used the **UNIQUE **function to collect all the unique names providing the dynamic range.

- Choose a
**cell**(**G5**), apply the below formula, and hit**ENTER**.

`=UNIQUE(B5:B15)`

- We will get all the unique names in the new column without dragging the
**Fill Handle**confirming the dynamic feature in Excel.

### Function 2 – Applying FILTER Function

- Select a
**cell**(**B18**), enter the following formula, and hit**ENTER**.

`=FILTER(B5:E15,(C5:C15=H4)*(D5:D15=H5),"no results")`

- We’ll get the filtered data from the table.

### Function 3 – Inserting SORT Function

- Choose a
**cell**(**G5**), insert the below formula, and press**ENTER**.

`=SORT(B5:E15,4,-1,FALSE)`

- It will sort selling price in descending order.

### Function 4 – Applying SORTBY Function

- Choose a
**cell**(**G5**), apply the below formula, and hit**ENTER**to get the result.

`=SORTBY(B5:E15,D5:D15,1,E5:E15,-1)`

- We sorted the region in ascending order and the selling price in descending order.

### Function 5 – Using RANDARRAY Function

- Choose a
**cell**(**D5**), add the following formula, and hit**ENTER**.

`=RANDARRAY(11,1,1000,9000,TRUE)`

- It generates random numbers for the chosen cells for product ID.

### Function 6 – Using SEQUENCE Function

- Choose a
**cell**(**B5**), add the following formula, and hit**ENTER**.

`=SEQUENCE(11)`

- The cells are filled with sequential numbers.

### Function 7 – Inserting TEXTSPLIT Function

- Choose a
**cell**(**D5**), add the formula, and hit**ENTER**.

`=TEXTSPLIT(B5:B14," ")`

- The texts are split extracting all the first names from the column.

### Function 8 – Applying TOCOL Function

- To combine the data in column G from three rows, use the following formula.

`=TOCOL(C4:E6)`

- The data will get rearranged in a single column.

### Function 9 – Applying TOROW Function

- Choose a
**cell**(**F4**), add the formula, and hit**ENTER**.

`=TOROW(B5:B10)`

- The range of data gets arranged row-wise. We organized the Student Names and IDs from column-wise to row-wise.

### Function 10 – Applying WRAPCOLS Function

- Choose a
**cell**(**G5**), enter the formula, and hit**ENTER**.

`=WRAPCOLS(B5:B14,5)`

- The names will get grouped from a single column to multiple cells.

### Function 11 – Using WRAPROWS Function

- Choose a
**cell**(**B8**), add the formula, and hit**ENTER**.

`=WRAPROWS(B5:I5,2)`

- It will group and rearrange multiple names from the list.

### Function 12 – Inserting TAKE Function

- Choose a
**cell**(**G5**), apply the formula, and hit**ENTER**.

`=TAKE(B5:E15,3)`

- It will return the first three rows as an array.

### Function 13 – Inserting DROP Function

- From the whole range of data, we copied the Region and Selling Price column to a new location. Select a
**cell**(**G5**), add the formula, and hit**ENTER**.

`=DROP(B5:E15,,2)`

- The data will be copied to the chosen location.

### Function 14 – Applying EXPAND Function

- Choose a
**cell**(**G4**), enter formula, and click**ENTER**.

`=EXPAND(B4:E15,13,5,"*")`

- The data will expand both row and column-wise. We used 13 inside the formula as we wanted to expand the rows to 13th, inserted 5 columns starting from the left, and placed
**asterisk (*)**in the last argument to return it in the expanded cells.

### Function 15 – Using CHOOSECOLS Function

- We will copy the 2nd and 4th columns from the table.
- Select a
**cell**(**G4**), add the below formula, and press**ENTER**.

`=CHOOSECOLS(B4:E15,{2,4})`

- The columns are placed in a new place in the spreadsheet.

### Function 16 – Applying CHOOSEROWS Function

- Choose a
**cell**(**G4**), apply the formula from below, and press**ENTER**.

`=CHOOSEROWS(B4:E15,{1,4,7,9})`

- The chosen rows 1, 4, 7, and 9 will be copied to a new place.

### Function 17 – Using XLOOKUP Function

**The XLOOKUP function** allows you to search for values in dynamic arrays. We searched for all the values with the provided criteria which is the Salesperson named John Doe.

- Choose a
**cell**(**C18**), add the below formula, and press**ENTER**.

`=XLOOKUP(B18,B5:B15,C5:E15)`

- We will get all the values from the table matching the criteria.

### Function 18 – Applying VLOOKUP Function

**The VLOOKUP function **in Excel is a powerful and widely-used lookup function that stands for vertical lookup. We will use the **VLOOKUP **function with multiple lookup values which are salesperson to collect their product’s selling price.

- Choose a
**cell**(**C18**), add the formula, and hit**ENTER**.

`=VLOOKUP(C17:D17,B5:E15,4,FALSE)`

- We will get both the selling prices for the selected salesperson.

### Function 19 – Inserting HLOOKUP Function

**The HLOOKUP function **known as the horizontal lookup function searches for a value in the top row of a table and returns a result from the specified row within the same column. We will extract the first 3 regions from the same column.

- Choose a
**cell**(**C18**), add the following formula, and hit**ENTER**.

`=HLOOKUP(C17,B4:E15,{2,3,4},FALSE)`

- It gives the first 3 region names from the column.

### Function 20 – Using XMATCH Function

The **XMATCH **function returns the relative position of a specific value from a dynamic range. We will use the **XMATCH **function to search for the position of the row where the Salesperson named Emily Brown exists.

- Select a
**cell**(**H5**), apply the below formula, and click**ENTER**.

`=XMATCH(G5,B5:B15,0)`

- You will get the position of the cell in the worksheet.

Get FREE Advanced Excel Exercises with Solutions!

Thanks you very mush

please I want to know if there’s a function that can arrange columns base on their date by rows

Hello

Nafiu Gide,There are functions that can arrange columns based on their date by rows. To combine sorting columns by dates and rearranging rows dynamically, you can use the SORT function along with INDEX to reference the sorted columns and match the data accordingly.

Formula:

=INDEX(A2:D5,,MATCH(SORT(A1:D1),A1:D1,0))This formula sorts the dates in A1:D1 and aligns the data in A2:D5 accordingly.

Regards

ExcelDemy