Home > Blockchain >  apply indirect arrayformula to the entire column in gsheet
apply indirect arrayformula to the entire column in gsheet

Time:06-22

I get address of non-null cells from the formula below.

=arrayformula(if(A2:A="","",ArrayFormula(address(1,(mmult(isnumber(find("",A2:O99999))*1,sign(row(1:15))))))))

And I want values at the address I get from the formula. What should I add to the formula to get those values?

I want to insert the formula to "laststatus"

id laststatus ... statusA statusB statusC
A statusC ... done done done
B statusA ... done
C statusB ... done done
D statusC ... done done done

Thanks.

CodePudding user response:

You can use this formula:

=ARRAYFORMULA(REGEXEXTRACT(TRANSPOSE(QUERY(TRANSPOSE("|"&IF(C2:E5="done",$C$1:$E$1, "")),,COLUMNS(C2:E5))),"([^|]*)[|\s]*$"))

Output:

output

Reference:

  • enter image description here

  • Related