FILTER(condition OR condition) syntax?

Question

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?

Answer 1

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

Example

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

Reference

Google Product Forum, Boolean Expressions

Answer 2

=FILTER(A:F, REGEXMATCH(A:A, "Combat Medic|Universal"))

---

=QUERY(A:F, " where A matches 'Combat Medic|Universal'", 0)

Answer 3

If you have a large list of different values that you want to filter by you can JOIN them and use them within a regexmatch.

Instead of:

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

Try

=FILTER(Sheet2!A:F, REGEXMATCH(Sheet2!A:A, JOIN("|", <your_value_range>))

Example:

In the example below, I have a list of sales people & their sales. I want to find all sales over €150 by all people who received at least one customer rating of 5.