32

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?

43

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

-1

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

0


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

0

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.