arraysif-statementgoogle-sheetsformulanonblank

ISBLANK(...) = FALSE even thought the cell is blank in google sheet

enter image description here

Hi everyone,

I have 2 tables, 3rd column for Table 1 is Value 1 and 3rd column for Table 2 is Value 2. I combined these 2 tables by expanding both tables first so that all the columns are aligned as shown in the screenshot above (Column E to Column H).

The formula in all the yellow cells are:

Cell E4 : =QUERY(A4:C10,"Select A,B,C,' ' label ' ' 'Value 2' ")

Cell E12 : =QUERY(A12:C20,"Select A,B,' ',C label ' ' 'Value 1' ")

Cell K7 : =QUERY({E5:H10;E13:H17},"Select * where Col1 is not null",0)

Cell P7 : =ArrayFormula(IF(ISBLANK(M7:M12),100,M7:M12))

In column P, I want to return 100 as Value 1 if the cells in Column M is blank. So by right I should get 2,34,55,100,100,100 in column P but right now the formula still return 3 blank cells.

I suspect that is because the QUERY function that I used before which make the cell is not blank although it seems like still a blank cell. May I know is there any trick that I can use to find the blank cells in column M and column N (preferably don't touch the QUERY formula) since ISBLANK() is not working in this case?

Any help or advise will be greatly appreciated!

Edited

enter image description here

Solution

  • makes sense. you cant use ISBLANK because cell is not blank. remember that QUERY inserted an empty space.

    try:

    =ARRAYFORMULA(IF(ISBLANK(TRIM(M7:M12)), 100, M7:M12))

    ISBLANK is so sensitive that it will detect even residue from TRIM

    update:

    =ARRAYFORMULA(IF(TRIM(M7:M12)="", 100, M7:M12))