How to return "Empty" where a query returns empty cells Google sheets

Question

I have two columns. The first one contains names and the second some numbers. I am perfoming a query on the two columns but some of the cells in the second columns will not contain any value. I need to return "Empty" in those cells but I seem not to be able to with my formula. When I add ISBLANK to the query it throws an error.

My query =query($A$1:$B$20, "select A, B where (B <=80)")

Link to my spreadsheet https://docs.google.com/spreadsheets/d/12gDxvNONKYxqAPJa6FPGJMXkNv6wlXgHEMNjCg-Iuco/edit?usp=sharing

player0 · Accepted Answer · 2020-06-30 22:48:53Z

2

use:

=ARRAYFORMULA(IF(QUERY(A1:B, "where B <=80 and A is not null")="", 
        "Empty", QUERY(A1:B, "where B <=80")))

or:

=ARRAYFORMULA(QUERY({A2:A, ""&IF((A2:A<>"")*(B2:B=""), "Empty", B2:B), B2:B},
 "select Col1,Col2 where Col2='Empty' or Col3<=80", 0))

answered Jun 30, 2020 at 22:48

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Canned Man Over a year ago

Thanks, mate, this solved my problem as well: I was trying to add a simple if(a1="";"";) followed by the query in the then field, and this worked wonderfully.

Collectives™ on Stack Overflow

How to return "Empty" where a query returns empty cells Google sheets

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related