FILTER

FILTER(array ,include [,if_empty])

Returns the array after filtering data in a range that satisfies multiple conditions.

arrayThe array, or range to filter.
includeThe 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.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

 ABC
1=FILTER(B1:C4,B1:B4="two") = { "two", 20 }one30
2=FILTER(B1:C4,B1:B4="three") = { "three", 10 }two20
3=FILTER(B1:C4,B1:B4="one") = { "one", 30, "one", 50 }one50
4=FILTER(B1:C4,C1:C4<25) = { "two", 20, "three", 10 }three10
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.

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited Top