Google-sheets – FILTER(condition OR condition) syntax

filterformulasgoogle sheetsgoogle-sheets-queryregex

I am using FILTER() to display a value from Sheet 2 where the Name columns match.

My problem is where I want:

=FILTER(Sheet2!A:F, Sheet2!A:A="Combat Medic" OR Sheet2!A:A="Universal")

as OR results in an error.

What is the correct syntax?
Must I use a QUERY() instead?

Best Answer

Use the following formula to obtain the query you want.

Formula

=FILTER(A:A;(A:A="Combat Medic")+(A:A="Universal"))

Explained

In the reference it is explained by Ahab that + and * can be used as boolean expressions, as OR and AND.

The solution with the IFERROR (see revision history) is of no use in this case, unless you want to find a particular string:

=FILTER(Sheet2!A:F;IFERROR(FIND("Combat";A:A);0) + IFERROR(FIND("Uni";A:A);0))

Here the IFERROR returns 0 when nothing is found, causing the formula to still function.

Screenshot

enter image description here

Example

I've modified the example file to contain both solutions: Filter with Boolean

Reference

Google Product Forum, Boolean Expressions

Related Topic