How to Create an Array of Every Occurrence Based on a Condition in Excel

In Excel, an array is a collection of values that are stored in a single cell or a range of cells. Arrays can be used to perform calculations or operations on multiple values at once, such as summing, averaging, counting, or filtering. Arrays can also be used to store intermediate results or return multiple values from a formula.

One of the common tasks that involve arrays is to create an array of every occurrence of a value that meets a certain condition. For example, you may want to create an array of all the products that have a certain category, or all the employees that have a certain salary range, or all the dates that fall within a certain period. This can be useful for further analysis, such as finding the average, minimum, maximum, or count of the values in the array.

There are different ways to create an array of every occurrence based on a condition in Excel, depending on the version of Excel you are using and the complexity of the condition. In this article, we will explain some of the methods that you can use, and provide a detailed example with real data.

Methods

Using the FILTER function

The FILTER function is a new function that was introduced in Excel 365. It allows you to filter a range of data based on one or more conditions, and return the filtered data as an array. The syntax of the FILTER function is:

=FILTER (array, include, [if_empty])

where:

• array is the range of data that you want to filter.
• include is a Boolean array that specifies which rows or columns to include in the filtered data. The Boolean array can be created by using comparison operators, logical functions, or other functions that return TRUE or FALSE values.
• if_empty is an optional argument that specifies what to return if the filtered data is empty. You can enter any value or expression that you want, such as a text, a number, or an error value. If you omit this argument, the FILTER function will return #CALC! error if the filtered data is empty.

For example, if you have a table of products with their names, categories, and prices, and you want to create an array of all the products that belong to the category “Books”, you can use the following formula:

=FILTER (A2:C11, B2:B11="Books")

This formula will return an array of three rows and three columns, containing the names, categories, and prices of the products that have the category “Books”. You can enter this formula in any cell or range of cells that can fit the array, or use it as an argument for another function that can accept an array, such as SUM, AVERAGE, MIN, MAX, or COUNT.

Using the TEXTJOIN function

The TEXTJOIN function is another new function that was introduced in Excel 365. It allows you to join multiple text strings or arrays of text strings, with a specified delimiter, and return the result as a single text string. The syntax of the TEXTJOIN function is:

=TEXTJOIN (delimiter, ignore_empty, text1, [text2], ...)

where:

• delimiter is the text or character that you want to use to separate the text strings or arrays. You can enter any text or character that you want, such as a comma, a space, a dash, or a line break.
• ignore_empty is a Boolean value that specifies whether to ignore empty cells or arrays in the arguments. If you enter TRUE, the TEXTJOIN function will skip any empty cells or arrays and not include them in the result. If you enter FALSE, the TEXTJOIN function will include empty cells or arrays in the result as blank text strings.
• text1, text2, … are the text strings or arrays of text strings that you want to join. You can enter up to 252 arguments, and each argument can be a text string, a cell reference, a range of cells, or an array.

For example, if you have a table of products with their names, categories, and prices, and you want to create a text string of all the product names that belong to the category “Books”, separated by commas, you can use the following formula:

=TEXTJOIN (", ", TRUE, IF (B2:B11="Books", A2:A11, ""))

This formula will return a text string containing the names of the products that have the category “Books”, such as “The Art of War, Pride and Prejudice, The Catcher in the Rye”. You can enter this formula in any cell, or use it as an argument for another function that can accept a text string, such as LEN, FIND, or SUBSTITUTE.

Note that this formula is an array formula, meaning that you need to press Ctrl + Shift + Enter after typing the formula, instead of just Enter. This will tell Excel to evaluate the formula as an array, and return the result as an array. You can also use the new dynamic array feature in Excel 365, which allows you to enter a formula in a single cell, and spill the result to adjacent cells automatically, without using Ctrl + Shift + Enter.

Using the REPT function

The REPT function is an old function that has been available in Excel for a long time. It allows you to repeat a text string or a value a specified number of times, and return the result as a single text string. The syntax of the REPT function is:

=REPT (text, number_times)

where:

• text is the text string or value that you want to repeat.
• number_times is the number of times that you want to repeat the text or value.

For example, if you have a table of products with their names, categories, and prices, and you want to create a text string of all the product names that belong to the category “Books”, separated by commas, you can use the following formula:

=TEXTJOIN (", ", TRUE, REPT (A2:A11, B2:B11="Books"))

This formula will return the same result as the previous formula, but using the REPT function instead of the IF function. The REPT function will repeat the product name if the category is “Books”, or return a blank text string if the category is not “Books”. The TEXTJOIN function will then join the repeated product names with commas, and ignore the blank text strings.

Note that this formula is also an array formula, meaning that you need to press Ctrl + Shift + Enter after typing the formula, instead of just Enter. You can also use the new dynamic array feature in Excel 365, which allows you to enter a formula in a single cell, and spill the result to adjacent cells automatically, without using Ctrl + Shift + Enter.

Using other functions

There are other functions that can be used to create an array of every occurrence based on a condition in Excel, such as the INDEX, MATCH, SMALL, LARGE, ROW, COLUMN, and INDIRECT functions. These functions can be combined in different ways to create complex formulas that can filter, sort, or extract data from a range of cells. However, these formulas are usually more complicated and less intuitive than the ones we have discussed above, and may require more computation time and memory. Therefore, we recommend using the FILTER, TEXTJOIN, or REPT functions whenever possible, as they are simpler, faster, and more flexible.

Example

To illustrate how to create an array of every occurrence based on a condition in Excel, let us use a sample data set of employees with their names, departments, and salaries. The data set is shown in the table below:

Suppose we want to create an array of all the employees that have a salary above 4000, and display their names, departments, and salaries in a separate table. We can use the following steps to achieve this:

1. Select a range of cells that can fit the filtered data, such as G2:I6.
2. Enter the following formula in the first cell of the selected range, such as G2:

=FILTER (A2:C11, C2:C11>4000)

3. Press Enter, and the formula will spill the filtered data to the adjacent cells automatically, as shown in the table below: