so, i have a 4 columned spreadsheet of team player name salary and player role. what im trying to do is finding the player with most salary. i can find the highest salary with =max() function but i cannot find the other atrributes of the player who has that salary. i could do it with too many ifs but i have 350 players. so its not something to be done by ifs in my opinion. and the spreadsheet is also filtered so that means cell rows and columns are not stable.
and another problem i countered was that stating the statement: "the player with the highest salary in ‘a team name’ is ‘player name’. " and/or the statement: "highest amount of salary of players in ‘team name’ "
ex1: lets say Alex Rodriguez has the highest salary in the league and his salary is 25000000. i want a cell to show alex rodriguez by using this atrribute of his.
ex2. lets assume in red sox the highest salary is 30000000 and i want to show this in a cell by using this attribute.
i would say something like if a1:a30 = ‘team name’, max(c1:c30) but this obviously would not work… but by this syntax what im trying to tell is: in between a1 and a30 find ‘team name’ s and among those find the max value of the same row but in the c column
unfortunately i dont have the time for uploading but simply i can create a smaller one for you.
- team name - player name - salary - player role
- red sox - alex - 11000000- pitcher
- white sox - gary - 5000000 - catcher
so here is a simple example. original has 14 teams and averagely 29 players per team but the same format
i want to simply find the player name of the highest value under the salary column
but since the table is filtered i cannot just find the cell of this information basically it is not stable.
lets assume my mini table start from a1 so when i sort it by salary(descending) the information i want is in b2 but when i sort it by salary (acsending) its in b3…