Open In App

Dynamic Array Formulas in Excel

Improve
Improve
Like Article
Like
Save
Share
Report

Dynamic arrays are resizable arrays that calculate automatically and return value into multiple cells based on a formula entered in a single cell. The new array (multiple cells) that we get is known as spilling and the new array has been placed in neighboring cells. It is not necessary to use Ctrl + Shift + Enter to enter an array formula. Dynamic array formulas are only available in Excel 365 and Excel 2021.

Dynamic Array Formulas

Below mentioned array formulas are the dynamic array formulas, let’s learn in detail how these formulas are applied in excel on a certain dataset.

  1. UNIQUE
  2. SORT
  3. SORT BY
  4. SEQUENCE
  5. RANDARRAY
  6. FILTER
  7. LOOKUP

UNIQUE Formula

Unique formulas return a list of unique values in a list. Unique means that element that appears only once in an array. Values can be text, number, etc. A unique function is not case-sensitive means ‘Apple’ and ‘apple’ are the same. Syntax,

=UNIQUE(A3:A17)  

Applying-UNIQUE-formula

fig 1. Print only Unique Values 

SORT Formula

Sort formula returns a list in increasing order or decreasing order. Values can be text, number, etc. Syntax,  

=SORT(A4: A10) increasing order.

Sorting-in-increasing-order

fig 2. Sort the values

SORTBY Formula 

SORTBY formula sorts the contents of a range based on values from another array. Values can be text, number, etc. Syntax, 

=SORTBY(A4:A10,B4:B10,-1)

Here, -1 is used for only decreasing order.

Sorting-by-in-decreasing-order

fig 3. 

SEQUENCE Formula

The sequence formula allows you to generate a list of sequential numbers in an array. List can be 1D, 2D  determined by rows and columns arguments. Syntax, 

=SEQUENCE (5,6)   

It will generate five rows and 6 columns, so the value will be from1 to 30.

Generating-sequence

fig 4. Arrange sequence in row and column 

RANDARRAY Formula

RANDARRAY formula generates an array of random numbers. Generated values can be either decimals or whole numbers. The size of the array is specified by rows and columns arguments. Syntax,

=RANDARRAY(1,2)   

Generating-array-of-random-numbers

fig 5. Generate random value

FILTER Formula

The filter formula filters a range of data based on given criteria. It returned an array of filtered values. In this formula we print only those values which are greater than 100. Syntax,   

=FILTER(A4:A12,A4:A12>100)

Values-greater-than-100-printed

fig 6.  Using filter formula

LOOKUP Formula

The Excel LOOKUP function returns a value from a range (one row or one column) or an array. If you want to search any number from an array then we use this function. Syntax, 

=LOOKUP(value,  lookup _ range, [result _range])

For Example: when we use the lookup function so =LOOKUP(10502,A4:A9,B4:B9) then we got PETER ENGLAND as a resultant.

Lookup-formula-applied

fig 7. LOOKUP formula


Last Updated : 23 Jul, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads