Exceljet

Quick, clean, and to the point

Basic filter example

Excel formula: Basic filter example
Generic formula 
=FILTER(data,range=value,"not found")
Explanation 

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")
FILTER is a beta function available only through the Office Insiders program.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.