Excel Formula for Min Value with Tie Breaker Logic?

Question

I need an excel formula that can be used for the following logic. I have thousands of rows of data where I need to apply this logic, so doing it manually is out of the question.

I have thousands of scenarios that look like the grid below where I need to determine the Winner. The Winner is the column that has the lowest value.

Scenario 1 is simple because only column A has the lowest value. The problem is when there are ties, like in Scenario 2 and 3.

I've been given the following logic for tie breakers. When there are ties, the lowest rank determines the winner.

For example, in scenario 2, column A, C and E all tie for the lowest value of 3. Using the tie breaker logic, Column A wins because it has the lowest rank.

Answer 1

Your description says for tie breaks, the left most value wins. For this scenario, you can use Min to find the lowest value, then Match with Match_Type = 0 to fist the first occurance of the minimum value. Finally, use Index to return the header.

=INDEX($B$1:$G$1,MATCH(MIN(B2:G2),B2:G2,0))

_{If the order of tie breaks can change, this wont work}

---

If the order of tie breaks is not left to right, and you have Office 365, then

1. Get the minimum value
2. Filter the header list on that minimum value
3. Xlookup the filtered list in the tie break table, returning the tie break values
4. get the Minimum of those tie break values
5. XLookup that in the Tie Break headers column

=XLOOKUP(MIN(XLOOKUP(FILTER(TRANSPOSE($B$1:$G$1),TRANSPOSE($B2:$G2)=MIN($B2:$G2)),$J$3:$J$8,$K$3:$K$8,,0)),$K$3:$K$8,$J$3:$J$8,,0)