Top Ten Array Functions

Mastering Excel's array functions can completely transform how you handle data, letting you automate complex calculations and build dynamic reports with ease. Below are the top ten array functions you need to know, along with what they are used for and how to use them.

• FILTER

  • What it's used for: Used to extract a subset of data from a larger range based on criteria you define, automatically spilling the results into neighboring cells.

  • How to use it: =FILTER(array, include, [if_empty]). For example, =FILTER(A2:B10, C2:C10>100, "No Results") returns all rows where the value in column C exceeds 100.

• UNIQUE

  • What it's used for: Used to scan a range or array and return only a list of distinct, non-duplicate values.

  • How to use it: =UNIQUE(array, [by_col], [exactly_once]). For instance, =UNIQUE(A2:A50) creates a clean, vertical list of every unique entry found in that column.

• SORT

  • What it's used for: Used to sort the contents of a range or array automatically by a specified column or row in ascending or descending order.

  • How to use it: =SORT(array, [sort_index], [sort_order], [by_col]). For example, =SORT(A2:C10, 2, -1) sorts the range by the second column in descending order.

• SORTBY

  • What it's used for: Used to sort a range or array based on the corresponding values in a separate range or array, accommodating complex multi-level sorting logic.

  • How to use it: =SORTBY(array, by_array1, [sort_order1], ...). For example, =SORTBY(A2:B10, C2:C10, -1) sorts your main data using the values found in an external column C.

• SEQUENCE

  • What it's used for: Used to generate a sequential list of numbers (such as dates, serial numbers, or custom indexes) across rows and columns.

  • How to use it: =SEQUENCE(rows, [columns], [start], [step]). For example, =SEQUENCE(10, 1, 1, 1) generates a vertical list of numbers from 1 to 10.

• XLOOKUP

  • What it's used for: Used to search a range or array for a specific value and return a corresponding item from another column or row, serving as the modern replacement for VLOOKUP.

  • How to use it: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). For example, =XLOOKUP("Widget", A2:A100, B2:B100) finds "Widget" in column A and returns the matching value from column B.

• VSTACK

  • What it's used for: Used to append multiple arrays or ranges vertically on top of each other into a single continuous array.

  • How to use it: =VSTACK(array1, [array2], ...). For example, =VSTACK(A2:A10, C2:C10) stacks the contents of the first range directly above the second range.

• HSTACK

  • What it's used for: Used to append multiple arrays or ranges horizontally side-by-side into a single combined array.

  • How to use it: =HSTACK(array1, [array2], ...). For example, =HSTACK(A2:A10, B2:B10) merges two separate vertical columns into a single two-column table.

• TOCOL

  • What it's used for: Used to transform a multi-dimensional array or block of data into a single, continuous column.

  • How to use it: =TOCOL(array, [ignore], [scan_by_column]). For example, =TOCOL(A1:C5, 1) takes a 2D grid and flattens it into one column while ignoring blank cells.

• BYROW

  • What it's used for: Used to apply a custom LAMBDA calculation to each individual row of an array, returning a single aggregated result per row.

  • How to use it: =BYROW(array, lambda). For example, =BYROW(A2:C10, LAMBDA(row, SUM(row))) calculates the sum across every individual row in the specified range.