1

I'm using a series of IF and IF/AND functions to assign a set value according to, sometimes one set or two set of colums. While creating a range and Array Formula to apply that to all posible new columns, the IF/AND functions are disabled. All in Google Sheets.

Here is an example (both if/and are ommited from the result of the formula, they can only work if I remove the H2:H and J2:J ranges, that allow me to apply to all new rows :

=ArrayFormula(IF(AND(ISNUMBER(SEARCH("Maestría en Finanzas",H2:H)), ISNUMBER(SEARCH("EGADE en Línea",J2:J))),"MAFO",
IF(AND(ISNUMBER(SEARCH("Maestría en Finanzas",H2:H)), ISNUMBER(SEARCH("EGADE En Línea",J2:J))),"MAFO",
IF(ISNUMBER(SEARCH("Maestría en Finanzas Online",H2:H,1)),"MAFO",
IF(ISNUMBER(SEARCH("Maestría en Finanzas",H2:H,1)),"MAF",
"No Match"
))))))))))))
)))))))
0

2 Answers 2

1

AND does not work with array formulas; you have to use * instead. (Likewise, OR does not work with array formulas, and you must use + instead.) You also have redundancies in the formula.

It is always difficult to try to write a formula without access to the spreadsheet, layout, data types, etc. And, of course, there's no way to test a formula in the dark like this. But you can try this instead of what you have:

=ArrayFormula(IF(H2:H="",,IF(NOT(ISNUMBER(SEARCH("Maestría en Finanzas",H2:H))),"No Match","MAF")&IF(ISNUMBER(SEARCH("Maestría en Finanzas",H2:H))*(ISNUMBER(SEARCH("EGADE en Línea",J2:J))+(ISNUMBER(SEARCH("Maestría en Finanzas Online",H2:H)))),"O","")))

If this does not work, consider sharing a link to your sheet or to a copy of it.

0

I can't see anything wrong with your formula except that you have far too many closing brackets at the end.

This should work:

=arrayformula(IF(H2:H="",,IF(AND(ISNUMBER(SEARCH("Maestría en Finanzas",H2:H)), ISNUMBER(SEARCH("EGADE en Línea",J2:J))),"MAFO",IF(AND(ISNUMBER(SEARCH("Maestría en Finanzas",H2:H)), ISNUMBER(SEARCH("EGADE En Línea",J2:J))),"MAFO",IF(ISNUMBER(SEARCH("Maestría en Finanzas Online",H2:H,1)),"MAFO",IF(ISNUMBER(SEARCH("Maestría en Finanzas",H2:H,1)),"MAF","No Match"))))))


SAMPLE

sample

2
  • and() is an aggregating function and will not give row-by-row results in an array formula. Use boolean arithmetic instead. Sep 24, 2021 at 9:43
  • I'm not so sure about that. I DID test before I submitted my answer. To be honest, I was half inclined to add this answer as a comment since the OP didn't provide any data. But given that the OP's formula was so fouled up, I thought it best to confirm the structure of the formula and let the OP deal with the implications in their data.
    – Tedinoz
    Sep 25, 2021 at 2:27

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.