0

Currently I am using this function to copy/paste rows from the Mastersheet that have a cell that consists of the value "Beleggersprofielen" to my worksheet.

=FILTER( Recherche!B4:N , Recherche!O4:O1002 = "Beleggersprofielen")

But I would like this function to also check whether another column consists of another value. Because than it that specific row may also be copy/pasted.

I tried the following:

=FILTER( Recherche!B4:N , Recherche!O4:O1002 = "Beleggersprofielen"| Recherche!C4:C1002 = "11 - 20" )

But this gives error: Parse Error

How can I use a Logical OR Operator in my function?

EDIT

I tried the following

=FILTER( Recherche!B4:O,(Recherche!M4:M1002="Beleggersprofielen")+(Recherche!E4:E1002="11 - 20")+(Recherche!E4:E1002="21 - 50")+(Recherche!E4:E1002="51 - 100")+(Recherche!E4:E1002="101 - 250")+(Recherche!E4:E1002="251 - 500")+(Recherche!E4:E1002="501 - 1000"))

This results in: success.

  • I will, delete the last edit, thank you for the heads up! – Mieer Darwesh Nov 2 '16 at 15:00
4

Formula

Instead of

=FILTER( Recherche!B4:N , 
Recherche!O4:O1002 = "Beleggersprofielen"| Recherche!C4:C1002 = "11 - 20" )

Note: Added a breakline for readability. This haven't affect how the formula works.

Try

=FILTER( Recherche!B4:N1002,
(Recherche!O4:O1002="Beleggersprofielen")+(Recherche!C4:C1002="11 - 20"))

Note: FILTER require that the arguments be of the same size

Explanation

  • For scalar formulas use OR, for arrays use + or ADD.

  • The pipe character could be used as OR operator in functions that allow regular expressions, FILTER isn't one of them.

  • OR function allows scalar values, not arrays.

  • In Google Sheets, booleans are coerced as 1 (TRUE) and 0 (FALSE) in some functions. Other may require the use of N.

  • Due to the precedence rules, enclose each comparison between parenthesis.

  • That worked, thanks! Pls see EDIT 1 – Mieer Darwesh Nov 2 '16 at 14:46
  • Add +(Re‌​cherche!E4:E1002=string) for each string where string is "21-50", etc. – Rubén Nov 2 '16 at 14:49
  • So in my case that would mean 4 or 5 times such an addition? – Mieer Darwesh Nov 2 '16 at 14:59
  • That is one alternative. There are many others. – Rubén Nov 2 '16 at 15:11
  • Would you be so kind to look at my application as a response to your 'String' answer ? (EDIT) – Mieer Darwesh Nov 2 '16 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.