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]*$"))