Search code examples
excelexcel-formula

Apply FILTER formula to array (and use filter criteria on specific column in the array)

0 A B C
1 Project A 500
2 Project B 200
3 Project B 600
4 Project C 300
5 Project D 100
6
7 Project A 500
8 Project B 200
9 Project B 600
10 Project C 300
11 Project D 100

In Range A7:B11 I have created an array using this formula:

=HSTACK(A1:A5;C1:C5)

Now, I want to apply the FILTER formula to this array and filter out only the values that are =Project B so in the end the list looks like this:

0 |    A      |  B  |   C  |
--|-----------|-----|------|--
  |           |     |      |
7 | Project B | 200 |      |
8 | Project B | 600 |      | 
9 |           |     |      |

I tried something like this:

=FILTER(A7#;A7#="Project B")

It returns #VALUE!

I assume the issue is that somehow I need to tell the Filter formula that the filter should be applied to the first column of the array.

Do you know how to change the formula to make it work?

Solution

  • The filter-argument is applied on one column (might even be an unrelated column somewhere else), there's a few option:

    First: =FILTER(A7#,A7:A11="Project B")

    Opt 1

    Second: =FILTER(A7#,TAKE(A7#,,1)="Project B")

    Opt 2

    Option 2 keeps your range variable.