Hi everybody and being the first question in 2020 I wish everybody an Excellent new year!
Somebody asked me a question and I can't find the proper way to solve his question, so I hope on some support here on the Forum.
A simple table, and how to find out what is the score of the highest woman (and who is it (although that was not the question given)).
I wrote it down in attached Excel and give also my first thoughts about it.
See the table as a dummy, the real one is much bigger and wider.
Thanks for pointing out the good direction!
Frans
The reason the first table changes is becuase rows 7 & 10 are static, so if you re-sort, the max is still lookig at rows 7 & 10 which change data.
I have added a helper column so we get the max per row - in column J - =MAX(G22:I22)
If you are using office 365, you can use Maxifs
=MAXIFS(Tabel23[Help],Tabel23[man/woman],"w")
Office 2016, you can use Max & if together
=MAX(IF(Tabel23[man/woman]="W",Tabel23[Help],FALSE))
Then using the above you can index and match to get the Woman with the highest (might need ctrl,alt, del in 2016 but struggling to check at the moment)
=INDEX(Tabel23[Player],MATCH("W"&A23,Tabel23[man/woman]&Tabel23[Help],0))
I think you did it Purfleet! Thanks. It is working anyway and the helper column is the found I needed.
Only think I must check now is if this is possible in the old version the questioner uses.
I'll let you know. And if somebody else knows another solution, feel free to contribute because it helps to find other ways of thinking.
Frans
You could consider having 2 helper columns, one for M and another for W.
This will avoid having to use MAXIFS when using "older" version of Excel.
Just got in to work where we have Excel 2016 and Max and If [=MAX(IF(Tabel23[man/woman]="W",Tabel23[Help],FALSE))] does work.
Also the Index and Match does need CSE as it is an array formula.
Purfleet said
The reason the first table changes is becuase rows 7 & 10 are static, so if you re-sort, the max is still looking at rows 7 & 10 which change data.
I have added a helper column so we get the max per row - in column J - =MAX(G22:I22)
If you are using office 365, you can use Maxifs
=MAXIFS(Tabel23[Help],Tabel23[man/woman],"w")
Office 2016, you can use Max & if together
=MAX(IF(Tabel23[man/woman]="W",Tabel23[Help],FALSE))
Then using the above you can index and match to get the Woman with the highest (might need ctrl, Shift, enter in 2016 but struggling to check at the moment)
=INDEX(Tabel23[Player],MATCH("W"&A23,Tabel23[man/woman]&Tabel23[Help],0))
I was able to 'translate' the given formulas to the original table and it works without the CTRL-SHIFT etc.
I handed it over and am waiting for the reaction of the one who asked me if it works on his version.
Thanks also Sunny for your suggestion!
Frans
