Conditional VLookup

All,

This is my first post, looks to be a lot of really knowledgable people on here. So here we go.

I have 2 Columns in sheet1:

A - CIK
B - Date

In Sheet2
A - CIK
B - Date

For each row in Sheet1, I need to find if for a given CIK, date, if there is at least row with the same CIK with a date that is within 60 days before the said date in sheet1. If so, mark the row, "Found" in a new column for sheet1.

For example, for CIK=1, date=march 20, 2009, there could be many rows with CIK=1 in sheet2, but there should at least one between jan 20 2009 and march 20 2009, with the same CIk in sheet2. If so, mark the row "Found" in sheet1 or that row. Repeat this for all rows in sheet 1.

The two files contain atleat 70,000 rows each. So this should be fast.

I have done a lot of searching, but cannot seem to find the answer to these.

I appreciate the help.

Hi and welcome to the board,
if CIK and date in columns A and B on sheet 1 and also on sheet 2 this formula should help

=IF((SUMPRODUCT(--(Sheet2!A2:A9=Sheet1!A2);--(Sheet2!B2:B9>=(Sheet1!B2)-60)))>=1;"found";"")

Replace ; with , if needed

Adapt the ranges to your needs

Thanks for your solution. It works but with 70,000 rows it gets slow. Any ideas on mkaing it faster?

2007 has a sumifs function that might be faster