Forum Discussion
Double "if" conditions to filter rows
Hello,
i need help with Excel Formulas & Functions after i got a headache 😄
I need a formula that basically says "if in row B1 the text XY is present AND in row C1 the text XY is present, then say TRUE".
However, it is important that it includes some kind of "contains text", because it is only an excerpt of text that should be identical.
After we got that, I want to kind of filter the rows, so that I JUST have the rows with "TRUE".
But it's not possible to just use the default filter that you apply to headlines, because the data is "live" and expands daily, so it has to be sorted by date. I use Supermetrics for this to pull Google Ads data, if that means anything to anyone. In summary: I need to make it work that I ONLY have the rows that contain the same word.
For visualization here is a picture:
I only need the green rows, since the red word is identical here.
I tried multiple ways to get to my goal but nothing worked like expected.
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.
9 Replies
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
Will the text you're looking for ALWAYS be the part of column C before the space (or before the word Sale)?
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.
