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

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

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

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

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

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

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)

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

@Patrick2788

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

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

I did not realize you were using Google Sheets.
best response confirmed by Mr_FunGuy (Occasional Contributor)
Solution

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

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

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

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

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

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

Thanks! I ended up using the following:

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

Thanks a ton!