ArrayFormula for if Date is greater than Value

Question

I'm trying to create a formula which pulls data from a separate sheet and selects only rows which fulfill a date comparison.

This is what I have at the moment:

=ArrayFormula(if('Complete Staff List'!E2:E < Date(2017;1;1), 'Complete Staff list'!B:C))

So in theory, it should pull all employees who started before 2017.

I understand how ArrayFormula works, but I can't manage to get the 'if' operation to work with it.

Answer 1

If I am interpreting your question correctly, you want to use query, not arrayformula.

In an unused cell with room for results,

=query(B2:E,"select B,C,E where E < date'2017-01-01'")

Dates as criteria in query are particular. See the section on dates in Google sheets query functuion and examples at More query function examples. From a separate sheet, use a named range to make life easy.