公式返回列A包含范围的MAX值的行的值 [英] Formula returning Column A value for row containing MAX 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),选择最大的值(这里, 122.5
),并从列A中获取该值的人的姓名。如果我使用这个公式,它适用于范围B2中的值:B5:
= INDEX(A2:A5,MATCH MAX(B2:B5),B2:B5,0))
返回 Melissa
,但是如果我将范围扩展到不仅仅包含列B的值,我会得到一个#N / A
返回:
= INDEX(A2:A5,MATCH(MAX(B2:C5),B2:C5,0))
奇怪的部分(对我的简单大脑)是公式的MATCH部分工作正常,如果我只是放入这个公式,它从C3返回最高价值 122.5
= MAX B2:C5,B2:C5,0)
很明显当我使用它时会出错MATCH或INDEX命令。
希望这是有道理的,有人可以指出我的错误?
(A:A,MAX((B2:C5 = MAX(B2:C5))$($) * ROW(B2:C5)))
这是一个数组公式并且必须使用 Ctrl + Shift + 输入。
注意:Match只能一次搜索一个向量。它可以是一行或一列或一个数组。它不能是两行或更多行或二维数组。
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 highest value of the bunch (here, 122.5
) and returns the name of the person from Column A that got that value. If I use this formula, it works for the values in range B2:B5:
=INDEX(A2:A5,MATCH(MAX(B2:B5),B2:B5,0))
That returns Melissa
but if I expand the range to include more than just column B's values, I get an #N/A
returned:
=INDEX(A2:A5,MATCH(MAX(B2:C5),B2:C5,0))
The weird part (to my simple brain) is that the MATCH portion of the formula works fine, if I just put in this formula, it returns the highest value of 122.5
from C3:
=MAX(B2:C5,B2:C5,0)
So clearly something it going wrong when I'm using either the MATCH or INDEX commands.
Hopefully this makes sense and someone can point out my error?
Try this:
=INDEX(A:A,MAX((B2:C5=MAX(B2:C5))*ROW(B2:C5)))
This is an array formula and must be confirmed with Ctrl+Shift+Enter.
Note: Match can only search one vector at a time. It can be one row or one column or one array. It cannot be two or more rows or columns or a 2D array.
这篇关于公式返回列A包含范围的MAX值的行的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!