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.
Hello Larissa,
if you're searching for the same text string in both columns use this:
| IFERROR(IF(SUM(SEARCH("*string*,Column1:Column2))>0,1,0),0) |
if the string is different in each column use:
IFERROR(IF(SEARCH("*XYZ*",Column1)+SEARCH("*ABC*",column2)>0,1,0),0)
SEARCH is not case sensitive, and allows the use of wild cards: "?" is any one character, while "*" are any characters. Placing "*" before inside the quotes before and after the string a will find it anywhere in the cell.
If TRUE, Search returns a a number >0, representing the number of characters into the cell the string is found. If false it returns #N/A, this is why it checks for errors.
As written the search returns 1 if found in both, otherwise it returns 0, you can change it to return any value you wish.
Since the table is being updated with new values, you should generate a separate "Results Report" with your filtered results. (Separating the Data, Analysis, and Reporting/Results is a good habit to get into for all your workbooks)).
Id recommend a pivot table… then you can sort and filter, and calcualte other values as needed (for example: counting the number of matched lines vs non-matched; grouping lines by dates/periods, with the conversions subtitled for each grouping). If you aren't sure how to create a pivot table, respond in your comments and I'll update with quick instructions.
Hope this helps,
Aaron