How to turn a 2D array, based on some conditions, into a list (like a column)

Question

This question is a more general approach of this one:

The original question says:

I have a 2D array, and I want to see the blank cells in one column.

I would like to generalise this into:

I have a 2D array, and I want to see the cells, satisfying a condition, in one column.

Is there a general approach for this?

As an example, let's take this:

      A    B    C    D
   ==== ==== ==== ====
1 |   1   12    3    4
2 |  11   22   53   34
3 |   1   32   33    4

Imagine I'd like to list values, smaller than 5, in a column.

The result would need to be:

1
3
4
1
4
(without empty cells in between)

Is there a general approach (I mean a formula, not a VBA macro) for this?

Answer 1

You can use FILTERXML() & TEXTJOIN() if your version of excel supports.

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,A1:D3)&"</s></t>","//s[.<5]")

• <t> & <s> are used to make a xml string. We can use any charecter like <x>, <y> but must be in vaild xml format.
• Here "<t><s>"&TEXTJOIN("</s><s>",TRUE,A1:D3)&"</s></t>" will construct a valid xml string to process by FILTERXML().
• //s will return all s node while [.<5] will filter only nodes less than 5.

More about FILTERXML() here from @JvdV Extract substrings from string using FILTERXML()