arrays - Excel数组查找公式

Question

我有两个表，如下所示。对于公式，假设“ID1”位于单元格 A1 上，表格之间有一个空白行，因此“ID”位于单元格 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

在公式中，如果 id 位于该行，我想要最大日期的 id。因此，在本例中，a、b、d 中的最大日期为 d，即 1/4/2015。所以我想要公式输出d。

到目前为止我已经有了下面的内容，但是 #N/A 让它失败了。如果没有 N/A 值，下面将输出最大日期。但是，我想要最大日期的 ID。它应该忽略范围内的 N/A。请注意，表 1 中的所有 ID 都会出现在表 2 中。但表 1 中的某些 id 列可能不适用。

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

Answer 1

比预期更大、更复杂的公式，但它会考虑到日期可以在数据集中出现多次。请务必使用 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")

我还添加了一些额外的错误处理。如果无法找到匹配项，该公式将返回“No Match Found”。