Excel how to return an array that meets a certain condition?

Question

If I have data in cell range A1:A6 which is:

Apple
Banana
Cherry
Grape
Orange
Watermelon

Is there a way to return an array (in a single cell, for an intermediate step for a larger formula) which returns the above array except only those entries that satisfy a certain condition?

For example, if I wanted a formula to return an array of only those cells that contain the letter n, it would return this:

Banana
Orange
Watermelon

Is there a way to accomplish this?

Note I do not want to return an array of the same size, just with blank entries, i.e. I do not want:

""
Banana
""
""
Orange
Watermelon

Answer 1

Yes.

Here is the array formula (line break added for readability):

= INDEX(A1:A6,N(IF({1},MODE.MULT(IF(ISNUMBER(SEARCH("n",A1:A6)),
  (ROW(A1:A6)-ROW(A1)+1)*{1,1})))))

Note, this is an array formula, meaning you must press Ctrl+Shift+Enter after typing the formula instead of just Enter.

---

There's some particularly odd things about this formula so I thought I would explain what is going below if you are interested. Some of what I explain below is probably obvious, but I am just being thorough.

To return a result from a list based on a single index, use this:

= INDEX(A1:A6,2)

This would return Banana.

To return results from a list based on multiple indices, you would think to use something like this:

= INDEX(A1:A6,{2;5;6})

This would ideally return {Banana;Orange;Watermelon}.

However, this does not return an array. Based on a recent question that I asked, a very clever workaround to this problem was given:

= INDEX(A1:A6,N(IF({1},{2;5;6})))

This will return the desired result of {Banana;Orange;Watermelon}.

This I consider part 1 of the explanation.

---

Part 2 of the explanation is how the following returns {2;5;6}:

= MODE.MULT(IF(ISNUMBER(SEARCH("n",A1:A6)),(ROW(A1:A6)-ROW(A1)+1)*{1,1}))

MODE.MULT is a function that returns the data in a set that appears most frequently. There are a few caveats, however:

1. Data must appear at least twice to be returned by MODE.MULT. If there is no duplicate data, it will return an error. For example, MODE.MULT({1;2;3}) would return an error because there is no repeating data in the array {1;2;3}. Another example: MODE.MULT({1;1;2} would return 1 because 1 appears most often in the data.

2. If there is a "tie" in terms of what data appears the most, MODE.MULT returns an array of all tied entries. For example MODE.MULT({1;1;2;2}) would return an array of {1;2}.

3. Most importantly, and probably the most peculiar but also most useful behavior of MODE.MULT, MODE.MULT completely ignores logical values (TRUE and FALSE values) when determining the mode of the data, even if they appear more often than the non-logical values in the data.

We can exploit these properties of MODE.MULT to get the desired array.

ISNUMBER(SEARCH("n",A1:A6)) returns an array of TRUE/FALSE values where the data contains an n. Something like this:

FALSE
TRUE
FALSE
FALSE
TRUE
TRUE

(ROW(A1:A6)-ROW(A1)+1) returns an array starting at 1 and increasing by 1 to however large the original array is:

1
2
3
4
5
6

(ROW(A1:A6)-ROW(A1)+1)*{1,1} effectively just copies this column:

1  1
2  2
3  3
4  4
5  5
6  6

The IF statement is used to return the number in the array above if TRUE, and FALSE otherwise. (Since the IF statement contains no "else" clause, FALSE is the default value given.)

In this example, the IF statement would return this:

FALSE  FALSE
2      2
FALSE  FALSE
FALSE  FALSE
5      5
6      6

Taking MODE.MULT of the above formula will return {2;5;6} because as mentioned, MODE.MULT conveniently ignores the FALSE values in the array above when considering the mode.

It is necessary to consider the above array inside MODE.MULT instead of simply:

FALSE
2
FALSE
FALSE
5
6

Because as mentioned previously, MODE.MULT requires that at least two entries in the data are required to match for it to return a value.

Answer 2

=INDEX($A$2:$A$7,MATCH(1,(COUNTIF($C$1:C1,$A$2:$A$7)=0)*(FIND("n",$A$2:$A$7)>0),0))

This is an array formula so will need to entered with Ctrl+Shift+Enter

Input and output

• The INDEX returns an element from a range
• The MATCH allows us to find the position of the row(s) to return
• The COUNTIF is to make sure we only select unique values in the results
• The FIND returns only rows where there is a n present in the string

Answer 3

Try the following User Defined Function:

Public Function ContainsN(rng As Range) As String
    Dim r As Range
    ContainsN = ""
    For Each r In rng
        If InStr(1, UCase(r.Value), "N") > 0 Then ContainsN = ContainsN & Chr(10) & r.Value
    Next r
    ContainsN = Mid(ContainsN, 2)
End Function

Answer 4

I came across this post while searching for a solution to a similar problem: return an array of expiration dates for all batches of a specific SKU.

I transposed a filter function so that my array of Batch Exp dates would appear on the same line as my Inventory SKU.

=IFERROR(TRANSPOSE(FILTER('Batch EXP'!$A$2:$A$1000,('BatchEXP'!$B$2:$B$1000=Inventory!$R4))),"No Expiration")

Added a concatenate column onto my filter range where I could combine quantities (F4) associated with each expiration date (I4).

=CONCATENATE(TEXT(I4,"MM-DD-YY")," x ",TEXT(F4,"#,#"), " units")

It works great and returns all an array for SKUS that match the criteria I setout. Could even take one step further to only return batches > X %, qty, etc. Here is example of output for a SKU:

11-15-22 x 40 units 11-01-22 x 5,216 units