40

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?

1

3 Answers 3

52

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

4
2

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

0


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

0

0

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.

enter image description here

1
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Oct 14, 2022 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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