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

CodePudding user response：

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