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:
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 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