Basic filter example
=FILTER(data,range=value,"not found")
To query data and extract matching records, you can use the FILTER function . In the example shown, the formula in F5 is:
=FILTER(B5:E15,E5:E15=H4,"not found")
Which retrieves data where the State = "TX".
How this formula works
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 "no results" 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")
