Double "if" conditions to filter rows

LarissaM711 

The syntax of the FILTER function is

FILTER(array, condition, [if_empty])

The first argument specifies which values you want to filter - for example a range. In the sample workbook, this is the range A2:D10 on Sheet1

The second argument specifies the condition. It must evaluate to TRUE for each row to be included, and to FALSE for rows to skip. In the sample workbook, it is basically the same as the formula used in the conditional formatting rule, except that it now applies to the entire range instead of just to the first row.

We use

ISNUMBER(SEARCH(SUBSTITUTE(Sheet1!C2:C10," Sale",""),Sheet1!B2:B10))

SUBSTITUTE(Sheet1!C2:C10," Sale","") removes the word Sale from the values in column C on Sheet1 so that we are left with E-Bike or VHV.

SEARCH(SUBSTITUTE(Sheet1!C2:C10," Sale",""),Sheet1!B2:B10) returns the position of that phrase in the value of column B, if found. Otherwise it returns the error #VALUE!

So for row 2, for example, it returns the number 4 since PHV is found starting at the 4th character in B2.

But for row 4, it returns #VALUE! since VHV is not found in B4.

Finally, ISNUMBER(SEARCH(SUBSTITUTE(Sheet1!C2:C10," Sale",""),Sheet1!B2:B10)) returns TRUE if the value was found, FALSE if not. This is precisely what we need to filter the range.

The third argument is optional. It specifies what text to return if none of the rows in array satisfy the condition. I omitted this argument.