使用多个条件进行查找，其中一个为最大值

Question

我尝试在如下数据中查找员工的LOCATION (NAME)和他们最近一个月(最大的月份数字)的MANAGER在特定的QUARTER中：

NAME             LOCATION        MANAGER         QUARTER    MONTH
Ryan Smith       Sioux Falls     Rick James        3          7
Jane Doe         Tampa           Bobby Brown       3          7
John Rogers      Tampa           Tracy Lane        3          7
Ryan Smith       Sioux Falls     Rick James        3          8
Jane Doe         Denver          Thomas Craig      3          8
John Rogers      Tampa           Cody Davis        3          8

因此，如果我知道员工的姓名和我正在查找的季度，结果应该会显示他们的上一位经理是谁以及他们所在的位置，因为这些可能每月都会发生变化。

我使用了索引和匹配数组公式：

{=INDEX($B$2:$B$7,MATCH(A12,IF($D$2:$D$7=D12,$A$2:$A$7),0))}  

但这只提供了第一个匹配，而不一定是该季度的最近一个月。我尝试包含一个MAX函数，如下所示：

{=INDEX($B$2:$B$7,MAX($E2:$E7,MATCH(A12,IF($D$2:$D$7=D12,$A$2:$A$7),0)))}  

但这也没能让我成功。

我需要什么公式才能让它起作用？

Answer 1

我认为我会选择PivotTable，因为它的多功能性和速度：

​

​

Answer 2

我认为透视表可能是最好的选择，当向底层数据添加新条目时，可以很容易地使用过滤器对其进行修改。我正在处理一个带有公式的解决方案，但它需要您添加一个查阅列。

查阅列的公式为：=E6&" "&H6&" "&I6

​

​

我不清楚操作员将如何“输入”他的员工姓名和季度，所以我不得不假设它将在一个单独的列中：

​

​

B栏中的公式(很繁琐)是：

=VLOOKUP(A6&" "&MAX(IF(H1:H100=NUMBERVALUE(RIGHT(A6,1)),I1:I100)),$D$6:$G$11,3,FALSE)&", managed by "&VLOOKUP(A6&" "&MAX(IF(H1:H100=NUMBERVALUE(RIGHT(A6,1)),I1:I100)),$D$6:$G$11,4,FALSE)

但它是有效的，并且只要调整查找范围，就是可伸缩的。