Multi-worksheet get max value and position with text parameter

[stealthK9]

I have a spreadsheet with 6 worksheets. The worksheet quantity will not change. Each worksheet can only name a person once. But each person can be on multiple worksheets. There is one column that has numbers in it ranging from -10 to 10. I want to create a rankings worksheet (a 7th) that has a list of each person on the 6 other worksheets. This worksheet needs to search the largest value from the 6 other worksheets for each person. In addition, it needs to return that person's role that had the largest value.

Basically since my range includes negative values, the max function will not work.

=MAX(MAXIFS(PosA[Grade],PosA[Name],[@Name]), MAXIFS(PosB[Grade],PosB[Name],[@Name]), MAXIFS(PosC[Grade],PosC[Name],[@Name]), MAXIFS(PosD[Grade],PosD[Name],[@Name]), MAXIFS(PosE[Grade],PosE[Name],[@Name]), MAXIFS(PosF[Grade],PosF[Name],[@Name]))

To get the person's position where the max value came from - some sort of index or match seems appropriate, but I've struggled to figure out where to start.

 

[Fluff]

Hi & welcome to MrExcel.
How about for the position

=LET(v,VSTACK(PosA,PosB,PosC,PosD),INDEX(SORT(FILTER(v,INDEX(v,,2)=[@Name]),4,-1),3,1))

 

[stealthK9]

While not ideal, I manually pasted values only from the 6 worksheets into one hidden worksheet and sorted by grade. Then when I do index-match to find the name, it finds the first which is the largest grade and the position with that grade.

 

[Fluff]

Did you try the formula I suggested?