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

Improve this question

1 Answer 1

Reset to default
5

Another approach might be:

=query(Planning!A:L,"select * where C contains 'Java' and I contains 'pending' ")
Improve this answer
4
  • Thanks that works! Though I'd still like to know why AND wasn't working above...
    – George Mauer
    Commented May 18, 2017 at 20:03
  • 1
    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
    Commented May 18, 2017 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
    Commented May 18, 2017 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
    Commented May 18, 2017 at 22:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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