=Filter Function
The FILTER function allows you to filter a range of data based on criteria you define.
Syntax
=FILTER(array,include,[if_empty])
| array | The array or range to filter |
| include | The 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:
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:
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