Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

Thanks for your time.

share|improve this question
    
Did my solution work for you? –  Jacob Jan Tuinstra Apr 28 '13 at 19:06
1  
You might also find this useful: Google Spreadsheets: Filter a range by array –  Vidar S. Ramdal Nov 27 '13 at 12:18
add comment

1 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.

Example

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

Reference

Google Product Forum, Boolean Expressions

share|improve this answer
    
Interesting! Could you elaborate on how IFERROR(...) + IFERROR(...) works? –  Vidar S. Ramdal Nov 27 '13 at 12:16
    
@VidarS.Ramdal How about it? –  Jacob Jan Tuinstra Nov 27 '13 at 13:20
1  
Great! Nice trick! –  Vidar S. Ramdal Nov 27 '13 at 13:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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