Excel数组查询公式 [英] Excel Array Lookup Formula

问题描述

我有两张桌子如下。对于公式，假设ID1在单元格A1上，表之间有一个空行，因此ID位于单元格A4上。

  ID1 ID2 ID3 ID4 ID_OF_MAXDATE 
abd＃N / A formula_here 
 
 ID DATE 
a 1/1/2015 
b 1/2/2015 
e 1/3 / 2015 
d 1​​/4/2015 
g 1/5/2015 
  

在公式，我想要id的最大日期，如果id在该行。所以在这种情况下，从a，b，d - 最大日期是d与1/4/2015。所以我想要公式输出d。

我到目前为止还有以下，但是＃N / A丢掉了。没有N / A值，下面输出最大日期。但是，我想要最大日期的ID。它应该忽略范围内的N / A。请注意，表1中的所有ID将显示在表2中。但表1中的某些id列可能为N / A。

  = MAX（IF（A2：D2 = A7：A11，B7：B11））
  

解决方案

一个比预期更大和复杂的公式，但它会考虑到日期在数据集中可能会出现多次。确保并使用 CTRL + SHIFT + ENTER 输入。

  = IF（SUM（IFERROR （A2：D2，$ A $ 6：$ A $ 10,0）， ））大于0，LOOKUP（REPT（ Z，255），IF（MAX（IF（FREQUENCY（IFERROR（MATCH（移调（A2 ：D2），$ A $ 6：$ A $ 10,0）， ），ROW（$ B $ 6：$ B $ 10）-ROW（$ B $ 6）1），$ B $ 6：$ B $ 10））= IF（FREQUENCY（IFERROR（MATCH（移调（A2：D2），$ A $ 6：$ A $ 10,0），0），ROW（$ B $ 6：$ B $ 10）-ROW（$ B $ 6）1）， $ B $ 6：$ B $ 10），$ A $ 6：$ A $ 10）），找不到匹配）
  

还有一些额外的错误处理。如果找不到匹配，该公式将返回找不到匹配。

I have two tables as below. For formulas, assume "ID1" is on cell A1 and one blank row between tables so "ID" is on cell A4.

ID1  ID2  ID3  ID4     ID_OF_MAXDATE
a    b    d    #N/A    formula_here

ID     DATE
a      1/1/2015
b      1/2/2015
e      1/3/2015
d      1/4/2015
g      1/5/2015

In the formula, I want the id of the max date if id's in that row. So in this case, out of a,b,d - the max date is d with 1/4/2015. So the I want the formula to output d.

I have the below so far but the #N/A throws it off. Without the N/A value, the below outputs the max date. However, I want the ID of the max date. And it should ignore N/A's in the range. Note, all ID's in table 1 will appear in table 2. But some id columns in table 1 may be N/A.

=MAX(IF(A2:D2=A7:A11,B7:B11))

解决方案

A much bigger and complex formula than expected, but it will take into account that a date can appear more than once in the data set. Be sure and enter with CTRL + SHIFT + ENTER.

=IF(SUM(IFERROR(MATCH(A2:D2,$A$6:$A$10,0),""))>0,LOOKUP(REPT("Z",255),IF(MAX(IF(FREQUENCY(IFERROR(MATCH(TRANSPOSE(A2:D2),$A$6:$A$10,0),""),ROW($B$6:$B$10)-ROW($B$6)+1),$B$6:$B$10))=IF(FREQUENCY(IFERROR(MATCH(TRANSPOSE(A2:D2),$A$6:$A$10,0),0),ROW($B$6:$B$10)-ROW($B$6)+1),$B$6:$B$10),$A$6:$A$10)),"No Match Found")

I also put in some additional error handling. The formula will return "No Match Found" if it is unable to find a match.

这篇关于Excel数组查询公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！