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

1
votes

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

1
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

1 Answers

1
votes

use:

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

0

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))

0