=FILTER(array, include, [if_empty]) returns the rows of array where include is TRUE. The include test is a comparison over a column of the same height, such as B2:B16="North".
It returns every match, which is what lookups can't do: all of a rep's orders, every overdue invoice.
Conditions combine with arithmetic:
- and: multiply the tests,
(B2:B16="North")*(F2:F16>1000);
- or: add them,
(B2:B16="North")+(B2:B16="East").
Each test needs its own brackets. When nothing matches, FILTER returns #CALC!; give a third argument, such as "None", to show that instead.