×

All of Excel's Dynamic Array Functions

By Chris Newman •  Updated: 02/02/24 •  6 min read
A comprehensive list of all available Dynamic Array functions in Microsoft Excel

The Power Of Dynamic Array Functions

In the ever-evolving landscape of data analysis and spreadsheet management, Microsoft Excel has taken a monumental leap forward by introducing Dynamic Array Functions. This groundbreaking feature rolled out to Microsoft 365 users starting in 2020. It has revolutionized how professionals and enthusiasts alike interact with data within Excel.

This article will serve as your hub for all things dynamic arrays by providing a directory of these amazing functions.

What Are Dynamic Array Functions?

Dynamic Array Functions are a set of powerful tools in Excel that automatically return multiple values into a range of cells based on a single formula. This feature marks a departure from traditional functions that could only return a single value to a single cell.

With dynamic arrays, when you enter a formula that returns multiple values, Excel intelligently spills these values into adjacent cells, dynamically resizing the output range to accommodate all the results. This behavior is known as "spilling," and it allows for more dynamic and responsive data models.

Dynamic Array Functions have simplified complex tasks and introduced a level of efficiency and flexibility previously unimaginable in spreadsheet software.


List of Excel’s Dynamic Array Functions

The following table is a comprehensive listing of Excel functions that fall under the class of Dynamic Array (meaning they can output multiple results per formula). I've included the year each function was publicly released to Microsoft 365.

Array FunctionRelease YearParametersDescription
FILTER2020array, include, [if_empty]Filters a range of data based on the criteria you specify
LET2020name1, name_value1, calculation_or_name2, [name_value2], …Assigns names to calculation results and values
RANDARRAY2020[rows], [columns], [min], [max], [integer]Returns an array of random numbers
SEQUENCE2020rows, [columns], [start], [step]Generates a list of sequential numbers
SORT2020array, [sort_index], [sort_order], [by_col]Sorts the contents of a range or array
SORTBY2020array, by_array1, [sort_order1], ...Sorts the contents of a range or array based on the values in a corresponding range or array
STOCKHISTORY2020stock, start_date, [end_date], [interval], [headers], [properties1], …Returns historical data about a financial instrument (stocks, FX rates, Crypto)
UNIQUE2020array, [by_col], [occurs_once]Returns a list of unique values from a range or array
XLOOKUP2020lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]Searches a range or an array, and returns an item corresponding to the first match it finds
XMATCH2020lookup_value, lookup_array, [match_mode], [search_mode]Searches for a specified item in an array or range of cells, and then returns the item's relative position
ARRAYTOTEXT2022array, [format]Converts an array to a text where each item is separated by a delimiter
BYCOL2022array, [lambda]Applies a LAMBDA function to each column in an array and returns an array of the results
BYROW2022array, [lambda]Applies a LAMBDA function to each row in an array and returns an array of the results
CHOOSECOLS2022array, col_num1, [col_num2], ...Returns an array with columns chosen from the source array
CHOOSEROWS2022array, row_num1, [row_num2], ...Returns an array with rows chosen from the source array
DROP2022array, rows, [columns]Removes a specified number of rows and/or columns from an array
EXPAND2022array, rows, [columns], [pad_width]Expands an array to a specified size, filling new cells with a specified value or empty if not specified
HSTACK2022array1, [array2], ...Combines multiple arrays horizontally into one array
ISOMITTED2022valueReturns TRUE if the value is an omitted argument in LAMBDA, otherwise FALSE
LAMBDA2022parameter_or_calculation, …Defines a custom function
MAKEARRAY2022rows, columns, lambdaCreates an array by applying a LAMBDA function to each row and column index
MAP2022array1, lambda_or_array2, …Applies a LAMBDA function to each element in one or more arrays and returns an array of the results
REDUCE2022initial_value, array, lambdaReduces an array to a single value by applying a LAMBDA function to each element
SCAN2022initial_value, array, lambdaScans an array and returns an array of intermediate calculation results
TAKE2022array, rows, [columns]Returns a specified number of rows and/or columns from an array
TEXTAFTER2022text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]Returns the part of the text after a specific delimiter
TEXTBEFORE2022text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]Returns the part of the text before a specific delimiter
TEXTSPLIT2022text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]Splits text into rows and columns according to delimiters
TOCOL2022array, [ignore], [scan_by_column]Converts an array to a single column
TOROW2022array, [ignore], [scan_by_column]Converts an array to a single row
VALUETOTEXT2022value, [format]Converts a value to text
VSTACK2022array1, [array2], ...Combines multiple arrays vertically into one array
WRAPCOLS2022vector, wrap_count, [pad_width]Wraps an array into a specified number of columns, creating a new array
WRAPROWS2022vector, wrap_count, [pad_width]Wraps an array into a specified number of rows, creating a new array
GROUPBY2024*row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter array]Groups the rows of an array based on unique values in specified columns and applies a LAMBDA function
PIVOTBY2024*row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter array]Pivots an array by one column and aggregates another column using a LAMBDA function
[ ] = Optional input
*In Excel Beta

Dynamic Array Function Categories

It may also help to see these functions grouped into categories that correspond with what they do. Below, I have organized these Excel functions into 6 categories.

Lookup Dynamic Array Functions

  • XLOOKUP
  • XMATCH

Lambda Dynamic Array Functions

  • LAMBDA
  • BYROW
  • BYCOL
  • MAP
  • REDUCE
  • SCAN
  • MAKEARRAY
  • ISOMITTED

Text-Based Dynamic Array Functions

  • TEXTBEFORE
  • TEXTAFTER
  • TEXTSPLIT
  • VALUETOTEXT

Array Sizing/Shaping Dynamic Array Functions

  • VSTACK
  • HSTACK
  • TOROW
  • TOCOL
  • WRAPROWS
  • WRAPCOLS
  • TAKE
  • DROP
  • CHOOSECOLS
  • EXPAND

Summarizing Dynamic Array Functions

  • FILTER
  • SORTBY
  • UNIQUE
  • SORT
  • GROUPBY
  • PIVOTBY

Miscellaneous Dynamic Array Functions

  • STOCKHISTORY
  • SEQUENCE
  • RANDARRAY
  • LET

I Hope This Microsoft Excel Article Helped!

Hopefully, I was able to provide you with a great resource for understanding all the available Dynamic Array functions you can utilize in your Microsoft Excel formulas. If you discover any Excel functions that are missing from this listing, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.