Jul 19 2022 12:29 PM
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?
Jul 19 2022 12:31 PM
Jul 19 2022 01:37 PM
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"))))))
Jul 19 2022 02:10 PM
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) |
Jul 19 2022 02:22 PM
@Patrick2788
I've tried this as well already... I'm using google sheets, so perhaps I've gotta find a way around?
Jul 19 2022 02:29 PM
Jul 19 2022 02:40 PM
Jul 19 2022 03:38 PM
SolutionIn Google Sheets I was able to reproduce it with this formula:
=TRANSPOSE(INDEX(SORT(TRANSPOSE(A1:F2),2,0),,1))
Jul 20 2022 08:02 AM
Jul 20 2022 11:09 AM