I don't want to create a user on an excel forum so I'm posting this here first
This is my spreadsheet
Score Week Card Person 1 2 3 4 5 6 7 8 9 Total 1 1 Person A 4 4 4 4 4 4 4 4 4 36 1 1 Person C 3 3 3 3 3 3 3 3 3 27 1 1 Person D 5 5 5 5 5 5 5 5 5 45 1 1 Person E 4 5 4 5 4 5 4 5 4 40 1 1 Person B 6 5 6 5 6 5 6 5 6 50 1 1 Person F 5 6 5 6 5 6 5 6 5 49 2 2 Person A 4 5 4 5 4 5 4 5 4 40 3 3 Person A 5 5 5 5 5 5 5 5 5 45 2 2 Person B 6 6 6 6 6 6 6 6 6 54
I have this on a tab called "DATA"
ON a second tab called "OUTCOME" I want to look up the maximum "SCORE" value based on the person name
Assuming the cell holding the person name is in G3 on the tab "OUTCOME" I am using the formula
=MAX(IF(Data!$C$2:$C$10=$G$3,Data!$A$2:$A$10,0))
I have a number of problems with this formula
If I use it on the DATA tab, it works, but only for only Person A (returning the value of 3), for all other people it returns 0
If I use it on the OUTCOME tab, it doesn't work at all (returns 0 for everyone)
Even if I hard code the Person name
=MAX(IF(Data!$D$2:$D$10="Person B",Data!$A$2:$A$10,0))
The formula returns a 0
Or if I try to concantenate the expression
=MAX(IF(Data!$D$2:$D$10=""&$G$3&"",Data!$A$2:$A$10 ,0))
If anyone has a suggestion before I go create an excel forum user please let me know.
p.s. I despise excel....