Basic filter example

This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:E15, which contains the full set of data without headers. The include argument is an expression that runs a simple test:

E5:E15=H4 // test state values

Since there are 11 cells in the range E5:E11, this expression returns an array of 11 TRUE and FALSE values like this:

{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}

This array is used by the FILTER function to retrieve matching data. Only rows where the result is TRUE make it into the final output.

Finally, the "if_empty" argument is set to "not found" in case no matching data is found.

Other fields

Other fields can be filtered in a similar way. For example, to filter the same data on orders that are greater than $100, you can use FILTER like this

=FILTER(B5:E15,C5:C15>100,"not found")