excel - find data in one column in sheet that is not in another sheet if date is later

2
votes

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

   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

So I want to return from Sheet1:

A          B
9/5/2011   In 7
3/3/2007   In 7
3/5/2011   In 8
1
excelexcel-formulaspreadsheet

1 Answers

4
votes

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.

Put this in Sheet1, C1, and drag down

=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.

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:

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