The output contains only rows from the source data where all three columns have a value. To do this, we use three boolean expressions operating on arrays. The first expression tests for blank 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: The second expression tests for blank groups: Again, we are checking 11 cells, so we get 11 results: Finally, we check for blank room numbers: which produces: 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 we 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
