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")