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

Occasional Contributor

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?

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


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


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




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




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

Using Google sheets... How about a SortN function??
I did not realize you were using Google Sheets.
best response confirmed by Mr_FunGuy (Occasional Contributor)


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



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
Thanks! I ended up using the following:


Thanks a ton!