Excel Filter
The FILTER command subsets rows of data.
Usage
Filters
Given a worksheet like:
A |
B |
Alice |
1 |
Bob |
2 |
Charlie |
3 |
Danielle |
4 |
To subset the rows, try:
=FILTER($A$1:$B$10, $B$1:$B$10>2)
The returned value will be an array and spill, which is very likely intended here.
Lookups
The function can be used to lookup values from a separate table.
Given a worksheet like:
A |
B |
C |
D |
E |
Alice |
1 |
|
Alice |
|
Bob |
2 |
|
Danielle |
|
Charlie |
3 |
|
Ethan |
|
Danielle |
4 |
|
|
|
Column E can be populated like:
=FILTER($B$1:$B$10, $A$1:$A$10=D1, "NO MATCH ERROR MESSAGE")
This works even if the key in column D is non-unique.
However, if the key in column A is non-unique, then the returned value will be an array and spill. See here for help on fixing that.
Multiple AND Conditions
The second argument to the function is functionally a boolean array. To apply multiple conditions for a match, enclose the conditions in parentheses and multiply them.
=FILTER($B$1:$B$10, ($A$1:$A$10=D1) * ($B$1:$B$10>2), "NO MATCH ERROR MESSAGE")
Similarly, to match based on any of multiple conditions, use addition.
=FILTER($B$1:$B$10, ($A$1:$A$10=D1) + ($B$1:$B$10>2), "NO MATCH ERROR MESSAGE")
Note that, if the key in column A is non-unique, the returned value is ordered according to their original order, not according to which condition passed.