CODEWITHSUNDEEP
arraysregexif-statementgoogle-sheetsfilter
Michael
Michael

Reputation: 121

How can I return a string, "Empty", if an arrayFormula returns nothing?

I must admit I am god awful with these formulas. I originally got this formula from someone else on SO, but I still can't get it to return my own string if the arrayFormula returns an empty cell...

This formula is searching for a Photographer's name in the first Filter function, and then the Job Name that the photographer is assigned to in the second Filter function.

This information is then printed out in a dashboard like so: enter image description here

What I need to be able to do is print "Open" in a cell where there is no job found under that day (Th/F)

I've tried using

=ARRAYFORMULA(IF(ISBLANK(...

but can't get it to work.

https://docs.google.com/spreadsheets/d/1-oT9nSYB-w1_bA3VZ7SZpAHcgh044dLiLzv90K4RHv8/edit?usp=sharing

Upvotes: 1

Views: 146

Answers (1)

player0
player0

Reputation: 1

use:

=ARRAYFORMULA(SUBSTITUTE(REGEXREPLACE(TRIM(QUERY(IF(
 FILTER(FASHION!D12:Z183, MOD(COLUMN(FASHION!D12:Z183), 5)=0)=C5, 
 FILTER(FASHION!D6:Z177,  MOD(COLUMN(FASHION!D6:Z177)+1, 5)=0), ),,9^9)), 
 "(?i)bay ", ), " ", ", "))

enter image description here

Upvotes: 2

Related Questions