excel - 如果日期较晚，则在工作表中的一列中查找不在另一工作表中的数据

I have hundreds of records in sheet1 with various create dates and values. I need to find the records in sheet2 matching on column B

我在sheet1中有数百条记录,其中包含各种创建日期和值。我需要找到B列匹配的sheet2中的记录

Sheet1

   A           B
1. 10/1/2012   In 6
2. 9/5/2011    In 7
3. 3/3/2007    In 7
4. 3/5/2011    In 8

Sheet2

   A           B
1. 11/2/2012   In 6
2. 10/5/2005   In 8
3. 6/6/2011    In 9
....

So I need to return the records from sheet1 that either the column B value is not in sheet2 colB or the colB record is present but the date in colA is earlier than the record in sheet1. What formula should I use? I have a vague idea that the vlookup() function would be helpful but am not sure how I can limit the return to these conditions in Excel

因此,我需要从sheet1返回记录,即B列值不在sheet2 colB中,或者colB记录存在但colA中的日期早于sheet1中的记录。我应该使用什么配方?我有一个模糊的想法,即vlookup()函数会有所帮助,但我不确定如何限制在Excel中返回这些条件

So I want to return from Sheet1:

所以我想从Sheet1返回:

A          B
9/5/2011   In 7
3/3/2007   In 7
3/5/2011   In 8

1 个解决方案

#1

This can be broken into 2 expressions. First, we will look for Matches in Sheet2, where the Sheet2 Col A record is earlier than sheet 1.

这可以分为2个表达式。首先,我们将在Sheet2中查找匹配,其中Sheet2 Col A记录早于工作表1。

Put this in Sheet1, C1, and drag down

将它放在Sheet1,C1中,然后向下拖动

=IF(INDEX(Sheet2!A:A,MATCH(B1,Sheet2!:B:B,0))<A1,B1,"")

This says: Match B1 with column B in sheet2. Then, take that row number, and return the date on that row from column A in sheet2. Then, compare that with A1. If it's earlier than A1, show the record on B1. Otherwise, show blank.

这表示:匹配B1与sheet2中的B列匹配。然后,获取该行号,并从sheet2中的A列返回该行的日期。然后,将其与A1进行比较。如果它早于A1,则显示B1上的记录。否则,显示空白。

But, this will return an error if B1 isn't found as a match on Sheet2. So to pick up those records, we simply wrap it in an IFERROR statement. IFERROR says "Return x value. If in retrieving X there is an error, return y instead." So this is simply:

但是,如果未找到B1作为Sheet2上的匹配项,则会返回错误。因此,为了获取这些记录,我们只需将其包装在IFERROR语句中。 IFERROR说“返回x值。如果在检索X时出现错误,请返回y。”所以这很简单:

=IFERROR(IF(INDEX(Sheet2!A:A,MATCH(B1,Sheet2!:B:B,0))<A1,B1,""),B1)

#1