Filter Function

=Filter Function

The FILTER function allows you to filter a range of data based on criteria you define.



arrayThe array or range to filter
includeThe array from which you'd like to filter results by
[if_empty] (optional)The value to return if all values in the included array are empty (filter returns nothing)


In the following example: Illustrative image

we used the formula =FILTER(B3:E51,B3:B51=G3,"") to return all records for Laptop, as selected in cell G3, and if there are no laptops, return an empty string ("").

  • B3:E51 contains all our data and is our array.
  • B3:B51 contains just the data we want to filter by, and therefore is our include
  • B3:B51=G3, This 'included' range to be equal to what value is in G3. This will mean we'll only include values that match what is found in cell G3

Further Examples

You can Also combine multiple criteria: Illustrative image

we used =FILTER(B3:E51,(B3:B51=G3)*(C3:C51=G6)) to reference both G3 like in the above example, but also G6 where we have added a 'salesperson'. 

The Include syntax part: '(B3:B51=G3)*(C3:C51=G6)' can be put in parathesis and separated by a * which will act as an AND 

Still need help?