返回对应的最大值日期 [英] Return corresponding date of max value

问题描述

我正在寻找一个公式，该公式将为我提供订单最新版本的日期.为说明起见，该表如下所示:

I'm looking for a formula which will give me the date of the last version of the order. To illustrate, the sheet looks as follows:

  A               B               C
1 Sales Order     Version         Date
2 SO#12345        1               21/02/2013
2 SO#12345        2               28/04/2013
3 SO#12345        3               04/08/2013
4 SO#12345        4               27/09/2013
5 SO#54321        1               02/03/2014
6 SO#54321        2               31/03/2014
7 SO#54321        3               07/10/2014
8 SO#54321        4               13/12/2014

所以我想获得SO#12345版本4(29/09/2013)的日期.

So I would like to get the date of SO#12345, version 4 (29/09/2013).

我尝试了INDEX，MATCH，IF和MAX函数的几种组合，但似乎无法获得正确的值.

I've tried several combinations of INDEX, MATCH, IF and MAX functions, but I can't seem to get the right value.

它要么在版本列中为我提供了最大值的对应日期，要么在正确的版本号中给了我，但是我无法使它显示适当的日期.

It either gives me the corresponding date of the highest value in the version column or it gives me the correct version number, but I can't make it show the appropriate date.

我确实对这些公式使用了CTRL + SHIFT + ENTER，所以它们作为矩阵函数执行.

I did use CTRL+SHIFT+ENTER for these formulas, so they are executed as a matrix function.

推荐答案

再次假设订单号在E2中，请尝试:

Again, assuming the Order Number is in E2, try:

= LOOKUP(1,0/FREQUENCY(0,1/(1+(A2:A8 = E2)* B2:B8))，C2:C8)

致谢

这篇关于返回对应的最大值日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！