Google-sheets – AND in ARRAYFORMULA giving error

formulasgoogle sheets

I'm just getting myself confused here.

I have a Planning spreadsheet with people's name in A, their skills in C, and their status in I. I would like to create another sheet within this spreadsheet that contains just the rows where the skills contain the word Java and status contains pending. I'm having trouble figuring out how to do this

=FILTER('Planning'!A2:L900, 
  ARRAYFORMULA(
    AND(
      ISNUMBER(SEARCH("Java", 'Planning'!C2:C900)),
      ISNUMBER(SEARCH("pending", 'Planning'!I2:I900)) )))

this doesn't seem to work erroring with

FILTER has mismatched range sizes

It seems that I'm using AND incorrectly as

=FILTER('Planning'!A2:L900, 
  ARRAYFORMULA(
    ISNUMBER(SEARCH("Java", 'Planning'!C2:C900)) )))

works fine

Best Answer

Another approach might be:

=query(Planning!A:L,"select * where C contains 'Java' and I contains 'pending' ")