I've been messing around with a spreadsheet for a while now and got it mostly working besides one part.

I have two columns, if a cell in the first column is "1", but the cell in the same row but different column is "empty" I want to output an error message.

I can do that for a single set of cells very easily with an IF, like so

=IF(J35 = 1,IF(ISBLANK(K35) = TRUE,"error","ok"),"ok")

But I have around 165 rows that I'd need to check that for.

Does anyone know how I could achieve this?

enter image description here

(The 1's and 0's are tickboxes in my actual table, ticked = 1, unticked = 0)

  • Welcome. Please keep in mind that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. – marikamitsos Jan 10 at 16:39

The use of the ARRAYFORMULA is what you need.
Please try the following formula instead
(Note adjust the ranges J3:J11 and K3:K11 to your needs and since you have tick-boxes change 1 to TRUE)

=ArrayFormula(IF(J3:J11 = TRUE,IF(ISBLANK(K3:K11) = TRUE,"error","ok"),"ok"))

Please read more about ARRAYFORMULA

  • Hey, thanks for the reply. I actually tried to use the ARRAYFORMULA before, forgot to mention that. But my problem with that is, that it doesn't return one cell with the text "error", but it returns either "ok" or "error" for each cell. That's how it's supposed to work obviously. I've been messing around with it for a little bit now, but I can't seem to figure out a way to check that array for "error" and then only return it once. – 112tom Jan 10 at 18:20
  • Alright, after a lot of googling later, I now got this: =IF(ISERROR(MATCH("problem",ARRAYFORMULA(IF(J33:J38 = 1,IF(ISBLANK(K33:K38) = TRUE,"problem","ok"),"ok")),0)),"","ERROR") Which does exactly what I needed, thanks :) – 112tom Jan 10 at 19:47
  • Glad it worked out for you and were able to modify it to your needs. Please feel free to update my answer including your modification. – marikamitsos Jan 12 at 12:57

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.