microsoft excel - Searching and Inserting Data Based on Date Range

In another post, a formula was given that was meant to help search and insert an entry if the date associated with the entry was less than the date on another sheet. The formula was as follows:

=IF(

    INDEX([LargerSheet!*Range with Dates*],

       MATCH(

         IF([SmallerSheet!*First Date in Range*]<[LargerSheet!*First Date in Range],
         [SmallerSheet!*Range with Dates]),

       [SmallerSheet!*Range with Dates*],0)

    )>0,1,"")

However, this did not quite work as desired. I am wondering if it might just be that my problem is slightly different. Essentially what I would like to do is, for a given entry in the SmallerSheet, check the dates associated with multiple objects in the LargerSheet and return the most recent date from the LargerSheet only if Most Recent Date < SmallerSheet Date. I think what the formula above is doing right now is checking if the SmallerSheet date is more recent than at least one of the dates on the LargerSheet, which results in the statement always being true because each SmallerSheet entry has multiple entries associated with it in the LargerSheet going very far back in time. Is this an easy fix?

Answer

Assuming you can sort the data in your LargerSheet, you can solve your problem like this:

First do a custom sort on LargerSheet; sort first by Name (A-Z) and second by Date (Oldest to Newest). Now all the same name entries are grouped together, and the last entry in each group is the most recent date for that name.

--A-- --B-- Alice 2003-08-20 Alice 2005-01-01 Alice 2006-05-16 Bob 2001-08-19 Bob 2003-01-01 Bob 2004-05-15 Charlie 2004-08-19 : :

Then in the SmallerSheet, in a column next to each name, use the following formula (assuming that, like in LargerSheet, the name is in column A and the date in column B). Take out the white space and comments.

=IF( INDIRECT("LargerSheet!$B"& //Cell starting with "$B" and ending with MATCH($A1,LargerSheet!$A:$A,1) //row of the last date for the name. )<$B1, //Compare with SmallerSheet date INDIRECT("LargerSheet!$B"& //"Then" return LargerSheet date, MATCH($A1,LargerSheet!$A:$A,1) ),$B1) //"Else" return SmallerSheet date.

This should return the most recent date from the LargerSheet if it is less than the date from the SmallerSheet (for each name), or the date from the SmallerSheet if not.

If it is necessary to put more conditions on the date selection, try using the "AND" and "OR" logical functions, or change the test itself. For example, the following modification to the formula will return the most recent date from LargerSheet as long as it is within -2 days of the date in the SmallerSheet, AND also as long as the value in the C column of LargerSheet is greater than zero. As you can see, the formula starts to look unwieldy, so care should be taken to match parenthesis and check syntax. Using multiple columns to break the formula up into stages might help.

=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1))>=($B1-2),INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1))<$B1,INDIRECT("LargerSheet!$C"&MATCH($A1,LargerSheet!$A:$A,1))>0),INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1)),$B1)

Finally, you might approach this problem the other way: Add a column to LargerSheet that does a lookup on the single entry for the name in SmallerSheet, do a test, and return a TRUE/FALSE value based on the test.