Forum Discussion
Solved
Trying to use "If" array to pull data in order from highest stat to lowest
My starting formula for Q2 is: =if (K2=$B2,$B$1,(if(K2=$C2, $C$1,(if(K2=$D2,$D$1,(if(K2=$E2,$E$1,(if(K2=$F2,$F$1,(if(K2=$G2,$G$1,"false")))))))))) This gives me the first value associated with ...
In Google Sheets I was able to reproduce it with this formula:
=TRANSPOSE(INDEX(SORT(TRANSPOSE(A1:F2),2,0),,1))
to follow up, i've tried the following and could not get it to work..
=if (L2=$B2,(if(Q2<>$B$1,$B$1,
(if(L2=$C2, (if(Q2<>$C$1,$C$1,
(if(L2=$D2, (if(Q2<>$D$1,$D$1,
(if(L2=$E2, (if(Q2<>$E$1,$E$1,
(if(L2=$F2, (if(Q2<>$F$1,$F$1,
(if(L2=$G2, (if(Q2<>$G$1,$G$1,"false")))))))))))))))))))))))
and
=ArrayFormula((if(and(M2=$B2,index(B1:P10000,MATCH(FALSE,S2=Q2:R2,0))),$B$1,(if(and(M2=$C2,index(B1:P10000,MATCH(FALSE,S2=Q2:R2,0))), $C$1,(if(and(M2=$D2,index(B1:P10000,MATCH(FALSE,S2=Q2:R2,0))),$D$1,(if(and(M2=$E2,index(B1:P10000,MATCH(FALSE,S2=Q2:R2,0))),$E$1,(if(and(M2=$F2,index(B1:P10000,MATCH(FALSE,S2=Q2:R2,0))),$F$1,(if(AND(M2=G2,index(B1:P10000,MATCH(FALSE,S2=Q2:R2,0))),$G$1,"false")))))))))))))
=if (L2=$B2,(if(Q2<>$B$1,$B$1,
(if(L2=$C2, (if(Q2<>$C$1,$C$1,
(if(L2=$D2, (if(Q2<>$D$1,$D$1,
(if(L2=$E2, (if(Q2<>$E$1,$E$1,
(if(L2=$F2, (if(Q2<>$F$1,$F$1,
(if(L2=$G2, (if(Q2<>$G$1,$G$1,"false")))))))))))))))))))))))
and
=ArrayFormula((if(and(M2=$B2,index(B1:P10000,MATCH(FALSE,S2=Q2:R2,0))),$B$1,(if(and(M2=$C2,index(B1:P10000,MATCH(FALSE,S2=Q2:R2,0))), $C$1,(if(and(M2=$D2,index(B1:P10000,MATCH(FALSE,S2=Q2:R2,0))),$D$1,(if(and(M2=$E2,index(B1:P10000,MATCH(FALSE,S2=Q2:R2,0))),$E$1,(if(and(M2=$F2,index(B1:P10000,MATCH(FALSE,S2=Q2:R2,0))),$F$1,(if(AND(M2=G2,index(B1:P10000,MATCH(FALSE,S2=Q2:R2,0))),$G$1,"false")))))))))))))