Forum Discussion
Double "if" conditions to filter rows
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.
column C will always be "XY Sale".
For column B the "XY" will not always be in the same place
Thanks.
Select A2:D10 (or however far down the data go).
The active cell in the selection should be in row 2, for example A2.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=ISNUMBER(SEARCH(SUBSTITUTE($C2," Sale",""),$B2))Click Format...
Activate the Fill tab.
Select a color.
Click OK, then click OK again.
See the attached sample workbook.