Excel数组查找 [英] Excel Array Lookups

问题描述

我有一个excel行与一些id的。我想从下面的第二个表中的第一个表中查找ID，并获取最大日期的id。输出应该仅在表1中的行中的id。

为了参考，ID1在单元格A1中，ID在单元格A4中。

我发布了类似的问题这里，但我的要求已更改，并要再次发布。 @rwilson在较早的帖子中给了我一个有用的创意公式，但是我没有指出表2中将会有重复的。在下面的表2中，b出现两次但日期不同。下列数据样本行的公式输出应为b，因为它具有最大日期。我不能以任何方式对表进行排序，因为我不能控制它。我需要参考它以最新的日期输出ID。

  ID1 ID2 ID3 ID4 IDD maxdate 
bac＃ N / A公式
 
 ID日期
b 1/26/2015 
b 1/28/2015 
a 1/26/2015 
d 1​​/29/2015 
c 1/27/2015 
  

更新样本数据
此数据示例使用以下@ Marc的公式。输出应为b，但显示为d。原因是索引是基于最大日期。由于最大日期出现在d，索引输出d默认。有没有办法告诉索引/匹配仅在表1中显示的id的索引/匹配？

  ID1 ID2 ID3 ID4 id of maxdate 
bac＃N / A d（应为b）
 
 ID日期
b 1/26/2015 
c 1/26/2015 
a 1/26/2015 
d 1​​/28/2015 
b 1/28/2015 
  

为了参考，表1从单元格A1开始，表2从单元格A4开始。以下是单元格E2中的公式。

= INDEX（$ A $ 4：$ A $ 9，MATCH（MAX（IF（$ A $ 4： $ A $ 9 = IFERROR（A2， ），$ B $ 4：$ B $ 9,0），IF（$ A $ 4：$ A $ 9 = IFERROR（B2， ），$ B $ 4：$ B $ 9,0 ），IF（$ A $ 4：$ A $ 9 = IFERROR（C2， ），$ B $ 4：$ B $ 9,0），IF（$ A $ 4：$ A $ 9 = IFERROR（D2， ），$ B $ 4：$ B $ 9,0）），$ B $ 4：$ B $ 9,0））

更新＃2
这是基于@ XOR公式的输出。这是一个不起作用的情况。

  ID1 ID2 ID3 ID4 IDd id of maxdate 
bad＃N / A b（应为a）
 
 ID日期
b 1/26/2015 
c 2/26/2015 
a 1/31/2015 
d 1​​/29/2015 
b 1/30/2015 
  

解决方案

= IF（SUMPRODUCT COUNTIF（A2：C2，A5：A9）），LOOKUP（1,0 / FREQUENCY（0,1 /（1+（B5：B9 = AGGREGATE（14,6，B5：B9 /（COUNTIF（A2：C2，A5 ：A9）>）））），A5：A9），无匹配ID）

如果两个或更多个ID共享最大日期并且也出现在Table1中，那么您不指定哪个应该是首选？

关于

I have an excel row with some id's. I want to look up the id's from the first table in the second table below and get the id with the max date. The output should only be from the id's that are in the row in table 1.

For reference, "ID1" is in cell A1 and "ID" is in cell A4.

I posted a similar question here but my requirements changed and wanted to post this again. @rwilson in the earlier post gave me a creative formula that worked but I did not indicate that there would be duplicates in table 2. In table 2 below, "b" appears twice but with different dates. The formula output for the sample row of data below should be "b" because it has the max date. I cannot sort the table in any way as I don't control it. I need to reference it to output the id with the latest date.

ID1    ID2    ID3    ID4    id of maxdate
b      a      c      #N/A   formula

ID  Date            
b   1/26/2015           
b   1/28/2015           
a   1/26/2015           
d   1/29/2015           
c   1/27/2015           

UPDATED SAMPLE DATA This data sample uses @Marc's formula below. The output should be b but it shows as d. The reason is that the index is based off of the max date. Since the max date appears in d, index outputs d by default. Is there a way to tell the index/match to only index/match on id's that appear in table 1?

ID1 ID2 ID3 ID4     id of maxdate
b   a   c   #N/A    d (should be b)

ID  Date            
b   1/26/2015           
c   1/26/2015           
a   1/26/2015           
d   1/28/2015           
b   1/28/2015   

For reference, table 1 starts at cell A1 and table 2 starts at cell A4. Here is the formula in cell E2.

=INDEX($A$4:$A$9,MATCH(MAX(IF($A$4:$A$9=IFERROR(A2,""),$B$4:$B$9,0),IF($A$4:$A$9=IFERROR(B2,""),$B$4:$B$9,0),IF($A$4:$A$9=IFERROR(C2,""),$B$4:$B$9,0),IF($A$4:$A$9=IFERROR(D2,""),$B$4:$B$9,0)),$B$4:$B$9,0))

UPDATE #2 Here is the output based on @XOR's formula. Here is a case where it doesn't work.

ID1 ID2 ID3 ID4     id of maxdate
b   a   d   #N/A    b (should be a)

ID  Date            
b   1/26/2015           
c   2/26/2015           
a   1/31/2015           
d   1/29/2015           
b   1/30/2015

解决方案

=IF(SUMPRODUCT(COUNTIF(A2:C2,A5:A9)),LOOKUP(1,0/FREQUENCY(0,1/(1+(B5:B9=AGGREGATE(14,6,B5:B9/(COUNTIF(A2:C2,A5:A9)>0),1)))),A5:A9),"No Matching ID")

By the way, you don't specify which should be preferred if two or more IDs share the maximum date and also appear within Table1?

Regards

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