公式返回列A包含范围的MIN值的行的值 [英] Formula returning Column A value for row containing MIN value of a range
问题描述
假设我有下表:
ABC
1周1周2
2 Melissa 114.7 82.8
3麦克105.5 122.5
4安德鲁102.3 87.5
5丰富105.3 65.2
名称在列A中,周值位于第1行中(因此A1为空白,B1 =第1周,A2 = Melissa。)
我正在尝试构建一个公式,查看已知范围内的所有值(在本例中为B2:C5),选择最小值(这里为65.2),并返回该人的名称具有该值的列A(Rich)。
我有一个类似的问题,由Excel Hero回答的MAX 在这里,但是如果我将MAX更改为MIN,那么它不起作用,因为有一些空白细胞在范围内,那些被选中。
希望这是有道理的,有人可以帮助我吗?谢谢!
Mike
应该这样做: ((B2:C5 =(MIN(IF(B2:C5> 0,B2:C5)) ,9E + 99))))* ROW(B2:C5)))
这是一个数组公式,必须用 Ctrl + Shift + 输入。
< blockquote>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
解决方案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.
这篇关于公式返回列A包含范围的MIN值的行的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!