FILTER

FILTER(array ,include [,if_empty])

Returns the array after filtering data that satisfies multiple conditions.

array	The array, or range to filter.
include	The array containing boolean values to indicate the filtering criteria.
if_empty	(Optional) The value to return if all values in the "include" array are empty (filter returns nothing).

REMARKS

* This function was added in Excel 2021.
* For an illustrated example refer to the page under Advanced Functions
* This function is not case sensitive when matching text strings.
* This function does not support wildcards (? and *).
* This function can create a Dynamic Array Formula.
* This function returns multiple match results, not just the first match, but all the matches.
* If "array" and "include" are not the same size, then #VALUE! is returned.
* If "include" does not contain all boolean values, then #VALUE! is returned.
* If "if_empty" is left blank, the function cannot return no matches.
* If "if_empty" is left blank and the function returns no matches, then #CALC! is returned.
* The arguments do not need absolute references because the formula is only entered into one cell.
* You can use the SORT function to sort the data before it is displayed.
* You can use the SORTBY function to sort the data before it is displayed.
* You can use the UNIQUE function to return an array of unique values from a list, table or cell range.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B	C
1	=FILTER(B1:C4,B1:B4="two") = { "two", 20 }	one	30
2	=FILTER(B1:C4,B1:B4="three") = { "three", 10 }	two	20
3	=FILTER(B1:C4,B1:B4="one") = { "one", 30 ; "one", 50 }	one	50
4	=FILTER(B1:C4,C1:C4<25) = { "two", 20 ; "three", 10 }	three	10
5	=SORT(FILTER(B1:C4,C1:C4<25),2,1) = { "three", 10 ; "two", 20 }
6	=FILTER(B1:C4,C1:C4=70,"no data") = "no data"

1 - Show all the rows in the range "B1:C4", where column "B" has the value "two".
2 - Show all the rows in the range "B1:C4", where column "B" has the value "three".
3 - Show all the rows in the range "B1:C4", where column "B" has the value "one".
4 - Show all the rows in the range "B1:C4", where column "C" has a value less than 25.
5 - Show all the rows in the range "B1:C4", where column "C" has a value less than 25, sorted by column 2, in ascending order.
6 - If there are no matches found, then the "if_empty" text is displayed.