The math operation (addition) coerces TRUE and FALSE values to 1s and 0s:
The result is a single array like this:
This final array is delivered to the FILTER function as the include argument, and FILTER returns only rows that correspond to a 1. Or, to put it another way, FILTER removes rows that are zero.
Not mutually exclusive
In the example above, we are testing for two possible values in a single column of data. This means that the two tests are mutually exclusive — both tests can't return TRUE at the same time. However, if you are testing multiple columns/fields for values, there is the possibility that more than one logical test will return TRUE. In that case, the final array may contain numbers larger than 1 (i.e. TRUE + TRUE = 2). This makes a difference in some formulas. However, in this case, it doesn't matter, because FILTER will treat any non-zero value as TRUE when evaluating the include argument.
This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D14, which contains the full set of data without headers. The include argument is based on a logical test based on the ISNUMBER...
In this example, the goal is to use XLOOKUP to find the first "red" or "pink" record in the data as shown. All data is in an Excel Table named data in the range B5:E14. This means the formulas below use structured references . As a result, the...
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.