Trying to use "If" array to pull data in order from highest stat to lowest

[Mr_FunGuy]

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 the stat in k2. But with Q2 being correct, i want R2 to show the next value while not giving a duplicate answer. I'm stuck right here, wanting to convert the values in k2:p2 into their respective characteristics. ie R2 should show intelligence and s2 should show charisma and so on.

I've been against the wall on this for a few days now, and no where else to turn after many hours of research hahaha! Please help?

[#2]

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

[#3]

@Mr_FunGuy 

You may consolidate the logic of the formula by using AND:

=IF(AND(L2=$B$2,Q2<>$B$1),$B$1,IF(AND(L2=$C$2,Q2<>$C$1),$C$1,IF(AND(L2=$D$2,Q2<>$D$1),$D$1,IF(AND(L2=$E$2,Q2<>$E$1),$E$1,IF(AND(L2=$F$2,Q2<>$F$1),$B$1,IF(AND(L2=$G$2,Q2<>$G$1),$G$1,"false"))))))

[#4]

@Mr_FunGuy 

What version of Excel are you using? If you're using Excel 365 you can use this formula in cell Q2.

=SORTBY(A1:F1,A2:F2,-1)

[#5]

@Patrick2788 

I've tried this as well already... I'm using google sheets, so perhaps I've gotta find a way around?

[#6]

Using Google sheets... How about a SortN function??

[#7]

I did not realize you were using Google Sheets.

[#8 · Accepted solution]

@Mr_FunGuy 

In Google Sheets I was able to reproduce it with this formula:

=TRANSPOSE(INDEX(SORT(TRANSPOSE(A1:F2),2,0),,1))

[#9]

that does work for the first row, thanks! how would i make that able to drag down 10000 rows? i'm gonna be dealing with many thousands of enteries

[#10]

Thanks! I ended up using the following:

=TRANSPOSE(INDEX(SORT(TRANSPOSE({$B$1:$G$1;B2:G2}),2,0),,1))

Thanks a ton!