# 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?

-

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.

-
This looks great! Would it be possible to make it so that instead of looking for the most recent date from LargerSheet if less than SmallerSheet it looks for the most recent date from the LargerSheet if it's within 2 days (or n days) from the SmallerSheet date. I think I could make this edit but I need to learn about INDIRECT a bit more before I'll stop feeling like I'll mess it up. –  114 Jul 28 '14 at 18:54
In addition, is there a way to check if a value in another cell corresponding to the date satisfying the previous conditions equals some value? –  114 Jul 28 '14 at 20:10
I'm realizing there is actually a small issue with this approach. Sometimes the desired date is not the latest one. Sometimes there is an earlier date that meets the criteria that it is within 2 days of the SmallerSheet date that is overlooked. Is there a way to address this? I apologize for what might be considered changing around the goal here. I'd be happy to ask another question to address this instead. –  114 Jul 28 '14 at 20:21
You can add more conditions to the test using the "AND" and "OR" functions. I'll edit the post and try to show how that might work. –  jbmorch Jul 28 '14 at 20:45
If you are looking for a unique entry in SmallerSheet, it would be easiest to use VLOOKUP, which just searches down a column until it finds a match, then returns the corresponding data from another column. –  jbmorch Jul 29 '14 at 14:50

I'm going to give you a formula that works differently from the one you listed since MATCH returns the first function.

This formula is an array formula so is entered using CTRL+SHIFT+ENTER.

This assumes your large table is in column A. The date you want to compare is in cell E2.

`=MAX(IF(\$A\$1:\$A\$33<E2,\$A\$1:\$A\$33,0))`

For each cell in the large table it does an if statement. If the cell is less then E2 it returns its date (which Excel stores as a number) if the cell is greater then E2 it returns 0. By taking the max of all these if statements it returns the largest date that is less then E2.

-
Thanks, I'll take a look at this and see if it works similarly. EDIT: This is handy, but I'm not sure it works since it doesn't seem to address the fact that each entity has multiple dates associated with it and doesn't take an entity as an input. –  114 Jul 28 '14 at 18:12
I'm not sure what you mean by the fact that each entity has multiple dates or that it doesn't take an entity as an input as that wasn't specified in the question. An image of your data and expected results would be helpful. –  gtwebb Jul 28 '14 at 18:24
You're right, I should have been more clear in my question I think. I'll try to provide a bit more detail when possible. –  114 Jul 28 '14 at 18:26