返回最大值由Index&匹配查找 [英] Return Max Value of range that is determined by an Index & Match lookup
问题描述
我需要一个单元格来显示范围的最大值,该行的范围是由索引和匹配公式定义的。我知道这将是一个数组函数,但我很难得到正确的语法。这是我的数据。我已经列出了列字母和行号如Excel。
I need a cell to display the max value of a range who's row is defined by an index and match formula. I know this will be an array function but I'm struggling to get the syntax right. Here is what my data looks like. I have it laid out with Column Letters and Row Numbers like Excel.
使用下面的表作为参考,在第二个表中。当我在列中单元格 A1
和 y
中输入 b
B1
,单元格 C1
中的公式应返回值 35
因为 35
是由 A1确定的行中的列
和 C:F
中的最大值 B1
使用 INDEX
和 MATCH
Using the Table Below as reference, in a second table. When I enter b
in cell A1
and y
in column B1
, the formula in cell C1
should return the value 35
because 35
is the maximum value in columns C:F
on the row determined by A1
and B1
using INDEX
and MATCH
表1。
A B C D E F
1 a x 25 6 23 11
2 a y 39 15 42 19
3 b x 28 34 51 24
4 b y 27 19 15 35
5 b z 38 26 12 18
6 c x 12 19 22 15
现在...我想做的是创建一个公式通过中的
的最大值与
A
和 B
在单独的表中给出。对于这个例子,我们将在单元格 C1
中编写公式。基于 A = A的匹配,公式应该通过
和列 F
最大值 C
b B = y
(该公式告诉我们是行 4
)。在这种情况下,我想要的值将是 35
,因为它是最大的4列( C:F
)在行 4
。
Now... What I want to do, is to create a formula that finds the max of columns C
through F
in the row that matches the values in A
and B
that are given in a separate table. For this example we will write the formula in cell C1
. The formula should take the maximum of C
through F
based on a match of column A = b
and column B = y
(which the formula tells us is row 4
). The value I want in this case would be 35
because it is the max of the 4 columns (C:F
) on row 4
.
这是我的第二个表应该是这样的公式在行 C
This is what my second table should look like with the formula being in row C
表2。
A B C
1 b y 35
2 a x 25
3 b z 38
4 c x 22
我尝试过:公式在表2中,因此它没有在公式的匹配部分中明确声明,您还必须熟悉excel中的表以获取它)
I tried this: (the formula is in table 2 so it is not explicitly declared in the match portion of the formula. You'll also have to be familiar with tables in excel to get it)
=INDEX(MAX(Table1[C]:Table1[F]),MATCH([@A]&[@B],Table1[A]&Table1[B],0))
然后我用控件 + / kbd> + 输入到Array它。
I then wrap it with Control + Shift + Enter to Array it.
当我把 MAX
函数在 INDEX
的数组部分内。有没有办法呢?也许我应该使用完全不同的函数集?
The problem seems to come when I put the MAX
function inside the array portion of the INDEX
. Are there any ways around this? perhaps I should be using a completely different set of functions?
推荐答案
您不需要索引匹配公式。你可以使用这个数组公式。输入公式后,您必须按 CTL + SHIFT + ENTER 。
You don't need an index match formula. You can use this array formula. You have to press CTL + SHIFT + ENTER after you enter the formula.
=MAX(IF((A1:A6=A10)*(B1:B6=B10),C1:F6))
SNAPSHOT
这篇关于返回最大值由Index&匹配查找的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!