在實際工作中，我們經(jīng)常需要從某列返回數(shù)據(jù)，該數(shù)據(jù)對應(yīng)于另一列滿足一個或多個條件的數(shù)據(jù)中的最大值。
如下圖1所示，需要返回指定序號（列A）的最新版本（列B）對應(yīng)的日期（列C）。
圖1
解決方案1：
在單元格F2中輸入數(shù)組公式：
=INDEX(C2:C10,MATCH(MAX(IF(A2:A10=F1,B2:B10)),IF(A2:A10=F1,B2:B10),0))
注意這里有兩個IF子句，不僅在生成參數(shù)lookup_value的值的構(gòu)造中，也在生成參數(shù)lookup_array的值的構(gòu)造中。千萬不能忽略了這一要點，即如果采用以下簡單方法：
=INDEX(C2:C10,MATCH(MAX(IF(A2:A10=F1,B2:B10)),B2:B10,0))
盡管此公式構(gòu)造仍可以返回正確的值，但完全不能保證所有情況下都正確。原因是與條件對應(yīng)的最大值不是在B2:B10中，而是針對不同的序號。而且，如果該情況發(fā)生在希望返回的值之前行中，則MATCH函數(shù)顯然不會返回我們想要的值。
可以將上面的公式解析來驗證：
=INDEX(C2:C10,MATCH(MAX(IF({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE},{4;2;5;3;1;3;4;1;2})),B2:B10,0))
轉(zhuǎn)換為：
=INDEX(C2:C10,MATCH(MAX({FALSE;FALSE;FALSE;FALSE;FALSE;3;4;1;2}),B2:B10,0))
轉(zhuǎn)換為：
=INDEX(C2:C10,MATCH(4,B2:B10,0))
轉(zhuǎn)換為：
=INDEX(C2:C10,MATCH(4,{4;2;5;3;1;3;4;1;2},0))
很顯示，數(shù)組中的第一個滿足條件的值并不是我們想要查找的值所在的位置：
=INDEX(C2:C10,1)
得到：
2013-2-21
這并不是滿足我們的條件對應(yīng)的值。
回到正確的公式：
=INDEX(C2:C10,MATCH(MAX(IF(A2:A10=F1,B2:B10)),IF(A2:A10=F1,B2:B10),0))
轉(zhuǎn)換為：
=INDEX(C2:C10,MATCH(4,IF(A2:A10=F1,B2:B10),0))
轉(zhuǎn)換為：
=INDEX(C2:C10,MATCH(4,IF({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE},{4;2;5;3;1;3;4;1;2}),0))
轉(zhuǎn)換為：
=INDEX(C2:C10,MATCH(4,{FALSE;FALSE;FALSE;FALSE;FALSE;3;4;1;2},0))
這次，參數(shù)lookup_array的數(shù)組中出現(xiàn)的最大值4與條件指定的序號相關(guān)，公式轉(zhuǎn)換為：
=INDEX(C2:C10,7)
結(jié)果為：
2014-10-7
解決方案2：
下面的公式更優(yōu)雅：
=LOOKUP(1,0/FREQUENCY(0,1/(1+(A2:A10=F1)*B2:B10)),C2:C10)
先看看公式中的：
(A2:A10=F1)*B2:B10
轉(zhuǎn)換為：
({12345;12345;12345;12345;12345;54321;54321;54321;54321}=54321)*B2:B10
轉(zhuǎn)換為：
({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE})*B2:B10
得到：
{0;0;0;0;0;3;4;1;2}
現(xiàn)在，來到公式中巧妙的部分。為了找到最大值在此數(shù)組中的位置（而不是像方案1一樣使用MATCH（MAX，…等）組合，那需要重復(fù)生成上述數(shù)組的子句），進(jìn)行如下操作：
我們首先給上面數(shù)組中的每個值添加一個小值。這是必需的，因為接下來將會對該數(shù)組中的值求倒數(shù)，如果不執(zhí)行此操作，則數(shù)組中的零將導(dǎo)致＃DIV / 0！錯誤，這會在將數(shù)組傳遞給FREQUENCY函數(shù)時使事情更復(fù)雜。
這樣：
1/(1+(A2:A10=F1)*B2:B10)
轉(zhuǎn)換為：
1/(1+{0;0;0;0;0;3;4;1;2})
得到：
{1;1;1;1;1;0.25;0.2;0.5;0.333333333333333}
這里的關(guān)鍵在于，將此數(shù)組作為bins_array參數(shù)的值傳遞給FREQUENCY函數(shù)，將零作為參數(shù)data_array的值。此時，公式構(gòu)造：
FREQUENCY(0,1/(1+(A2:A10=F1)*B2:B10))
轉(zhuǎn)換為：
FREQUENCY(0,{1;1;1;1;1;0.25;0.2;0.5;0.333333333333333})
求倒數(shù)之前數(shù)組中的最大值（即我們關(guān)注的值）為求倒數(shù)之后數(shù)組中的最小值。而且，如果我們傳遞一個所有值都在0到1之間的值數(shù)組作為FREQUENCY函數(shù)的參數(shù)bins_array的值，將0作為其參數(shù)data_array的值，那么零將被分配給參數(shù)bins_array中的最小值；其余的為空或為零。
由于數(shù)組中的最小值為0.2，在數(shù)組中的第7個位置，因此上述公式構(gòu)造的結(jié)果為：
{0;0;0;0;0;0;1;0;0;0}
獲得此數(shù)組后，我們只需要從列C中與該數(shù)組出現(xiàn)的非零條目（即1）相對應(yīng)的位置返回數(shù)據(jù)即可。因此，公式：
=LOOKUP(1,0/FREQUENCY(0,1/(1+(A2:A10=F1)*B2:B10)),C2:C10)
轉(zhuǎn)換為：
=LOOKUP(1,0/{0;0;0;0;0;0;1;0;0;0},C2:C10)
轉(zhuǎn)換為：
=LOOKUP(1,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!},C2:C10)
得到：
2014-10-7
