To query data and extract matching records, you can use the FILTER function. In the example shown, the formula in G7 is:

=FILTER(B5:E15,E5:E15=H4,"not found")

The result is recorded in the range B5:E15 where the State = "TX". Because FILTER returns more than one value, the results spill into the range G7:J10.

Generic formula

=FILTER(data,range=value,"not found")


In this example the goal is to return rows in the range B5:E15 that have a specific state value in column E. To make the example dynamic, the state is a variable entered in cell H4. When the state in H4 is changed, the formula should return a new set of records. This is a perfect application for the FILTER function, which is designed to return values that meet specific logical criteria from a set of data.

FILTER function

The FILTER function "filters" a range of data based on supplied criteria. In other words, the FILTER function will extract matching records from a set of data by applying one or more logical tests. The result is an array of matching values from the original data. The FILTER function takes three arguments, and the generic syntax looks like this:


In this problem, array is given as B5:E15, which contains the full set of data without headers:


The include argument is an expression that runs a simple test for matching states:

E5:E15=H4 // test state values

Placing this expression into FILTER as the second argument, we have:


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

=FILTER(B5:E15,E5:E15=H4,"not found")

Note the value for if_empty is a text string in double quotes (""). You can customize this message as you like. Supply an empty string ("") to display nothing. If you omit if_empty altogether, FILTER will return a #CALC! error when no data is returned.

When the formula above is entered, the include argument is evaluated. Since there are 11 cells in the range E5:E11 and the value in H4 is "TX", the include expression returns an array of 11 TRUE and FALSE like this:


Notice the TRUE values in this array correspond to records in the data where the State is "TX". 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.

Other fields and criteria

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

You can also configure the logic inside the include argument to apply more complex criteria.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.