IF STATEMENT BASED ON EQUAL VALUES IN RANGE

[mrmmickle1]

I am trying to compare cells within the range $T$7:$T$47. I look for the max value within this range. If two or 3 cells equal the same value within this range I would like it to compare another set of values. So for example if $t$7 and $t$9 both equaled say 15 and 15 was the Max of this range then I have another criteria set in cells $S$7:$S$47. If there is a "tie" for the max value in cells $t$7 and $t$9 (They would both = 15) then the cells adjacent to these ($S$7, $S$9) need to be compared against a value in cell $S$5. If the value in $S$7 = 35 and the value in cell $S$9 is 45 and the value in cell $S$5 is 39. I would like to take the absolute values of the differences between the cells so ABS($s$5-$S$7) = 4 and ABS($S$5-$S$9) = 6. Then take the lowest value so then in this instance Row 7 would win. I have values in the range $B$7:$B$47 that I would like to display if that cell wins so in this example I would want to display the value in cell $B$7.

[alansidman]

Suggest you post a sample spreadsheet with a before and after so that we can determine what you need based upon the information supplied.

[mrmmickle1]

Here is a sample spreadsheet. I was almost able to get it to work by adding a few helper columns. I tried to write a function called LookupWinner but I couldn't get it to work correctly. This spreadsheet is nothing crazy. Just a few drop down list boxes and nested if statements. It should be pretty self explanatory... I think I may have forget to take protection off the work book. Just use 1234 as the PW.

[Admin]

Hi

Try this array formula.

=IF(COUNTIF(T7:T47,MAX(T7:T47)),INDEX(B7:B47,MATCH (MIN(ABS(S5-S7:S47)),ABS(S5-S7:S47),0)),INDEX(B7:B47,MATCH(MAX(T7:T47),T7:T47, 0)))