excel - Formula returning Column A value for row containing MIN value of a range

Question

Assume I have the following table:

  A         B         C
1           Week 1    Week 2
2 Melissa   114.7     82.8
3 Mike      105.5     122.5
4 Andrew    102.3     87.5
5 Rich      105.3     65.2

The names are in column A, the Week values are in Row 1. (So A1 is blank, B1 = Week 1, and A2 = Melissa.)

I'm trying to build a formula that looks at all the values in a known range (in this example, B2:C5), chooses the lowest value of the bunch (here, 65.2) and returns the name of the person from Column A that got that value (Rich).

I got a similar question for the MAX answered by "Excel Hero" over here, but it doesn't work the same if I just change MAX to MIN because there are some blank cells in the range, and those get selected.

Hopefully this makes sense and someone can help me? Thanks!

Mike

Answer 1

This should do it:

=INDEX(A:A,MAX((B2:C5=(MIN(IF(B2:C5>0,B2:C5,9E+99))))*ROW(B2:C5)))

This is an array formula and must be confirmed with Ctrl+Shift+Enter.