3

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

5

Another approach might be:

=query(Planning!A:L,"select * where C contains 'Java' and I contains 'pending' ")
  • Thanks that works! Though I'd still like to know why AND wasn't working above... – George Mauer May 18 '17 at 20:03
  • Boolean functions such as AND(), OR() and NOT() have known weird behaviors inside array contexts, too. It's a common practice to replace them with arithmetics. I.e. instead of AND(cond1, cond1) just try cond1*cond2. Also, wrapping them in ARRAYFORMULA seems to be unnecessary. The FILTER already provides an array context. – ttarchala May 18 '17 at 21:09
  • Hi @pnuts, I'm calling them weird because I have run into issues in the past that I cannot explain or find any documentation on. Examples: webapps.stackexchange.com/questions/102470/… or productforums.google.com/forum/#!topic/docs/U541KrM_Amg -- if you can help with explaining that, it would be great. – ttarchala May 18 '17 at 21:47
  • @pnuts I've basically given up on Excel, using it at most to share data via email... but from what I remember, array formulas work quite a bit differently there, and of course there is no FILTER(), UNIQUE(), QUERY() etc. But I will give you just one example: OR(), AND() and NOT() do not work at all when applied inside an ARRAYFORMULA. I.e. they are not even expanded. Try =arrayformula(or(A:A="TextA", A:A="TextB")) and then for comparison =arrayformula((A:A="TextA")+(A:A="TextB")) – ttarchala May 18 '17 at 22:03

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.