The output contains only rows from the source data where all three columns have a value.
How this formula works
The FILTER function is designed to extract data that matches one or more criteria. In this case, we want to apply criteria that requires all three columns in the source data (Name, Group, and Room) to have data. In other words, if a row is missing any of these values, we want to exclude that row from output.
To do this, we use three boolean expressions operating on arrays. The first expression tests for blank names:
B5:B15<>""// check names
The not operator (<>) with an empty string ("") translates to "not empty". For each cell in the range B5:B15, the result will be either TRUE or FALSE, where TRUE means "not empty" and FALSE means "empty". Because there are 11 cells in the range, we get 11 results in an array like this:
When the arrays that result from the three expressions above are multiplied together, the math operation coerces the TRUE and FALSE values to 1s and 0s. We use multiplication in this case, because want to enforce "AND" logic: expression1 AND expression2 AND expression3. In other words, all three expressions must return TRUE in a given row.
Following the rules of boolean logic, the final result is an array like this:
This array is delivered directly to the FILTER function as the include argument. FILTER only includes the 6 rows that correspond to 1s in the final output.
To filter data to include only records where a value is this or that, you can use the FILTER function and simple boolean logic expressions. In the example shown, the formula in F5 is: = FILTER ( B5:D14 ,( D5:D14 = "red" ) + ( D5:D14 =...
To filter data to include data based on a "contains specific text" logic, you can use the FILTER function with help from the ISNUMBER function and SEARCH function . In the example shown, the formula in F5 is: = FILTER ( B5:D14 , ISNUMBER ( SEARCH...
To query data and extract matching records, you can use the FILTER function . In the example shown, the formula in F5 is: = FILTER ( B5:E15 , E5:E15 = H4 , "not found" ) Which retrieves data where the State = "TX". How this formula works...
The Excel FILTER function filters a range of data based on supplied criteria, and extracts matching records.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.