0

In a previous post I asked a question about how to find the most recent date associated with a particular entity and have that value returned in Sheet2 so long as it was greater than the associated date on Sheet2. I realized, however, that the true problem is not to have the most recent date returned if the condition is met, but to have the date returned if it is greater than and within 2 days (or generally n days) of the reference date.

The formula produced before was as follows:

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

Is there not a way to add another condition on INDIRECT() so that it asks not just that it is < $B1 but also that its distance to the date is <2?

I tried the following formula with no luck:

=IF(AND(ABS(INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))-$A2)<2,
INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2,INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2),INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1)),$A2)

A sample sheet can be found here.

Improve this question
1
  • You've got duplicate entries in LargerSheet - is that expected?
    – Iszi
    Jul 30, 2014 at 22:20

2 Answers 2

Reset to default
2

I had to back-track your series of questions to see where the problem is, but (with only minor modifications) @Hannu's formula is pretty much right. The problem you're having with implementing it is that your spreadsheet layout does not match what was specified by @jbmorch in their answer to your previous question.

The layout was very clearly specified in @jbmorch's answer, and matters, for a few reasons:

  • You did not specify much details about your spreadsheet layout in your question, except the parts of your psuedo-formula which included sheet names. So, @jbmorch and others had to speculate and create their own layouts in order to put together an appropriate formula.
  • Without ordering the columns in your sheet as specified in the answer, references to columns in the sheet will not be correct unless you adjust them.
  • The sorting order on LargerSheet is important because of the way MATCH functions - if you do not have the rows sorted as specified in the answer, MATCH will not produce accurate results.
  • @jbmorch's answer also presumed (because no information was provided otherwise) that your data started in row 1. Thus their answer was written for such a spreadsheet and will produce erroneus results if not adjusted for the actual layout.

A couple other possible problems to be aware of:

  • You need to make sure all date/time entries are actually formatted as dates & times, otherwise Excel cannot do proper comparisons. This can be checked in the format options under the cell properties, and also by trying math against the cell. (e.g.: If A2 contains 1/7/2003, and B2 is =A2+2, then the value for B2 should resolve to 1/9/2003).
  • Your sample sheet contains few entries in Sheet1 which actually match the criteria you're looking for to return a value from LargerSheet. This makes troubleshooting a bit troublesome. In fact, the only matching one I found was row 9. (The date for DAILY, JIM on that row in Sheet1 was actually within two days of the latest date for him on LargerSheet.)

That said, here's the formula you need. Drop it in C2 on Sheet1 and copy down:

=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))>B2,INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))<B2+2),INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1)),$B2)

Again, pay careful attention that your sheet is formatted exactly as below, otherwise the formula will not work without adjustments.

  • All dates must be formatted as dates - not as text or numbers.
  • Both sheets must have names in column A, and dates in column B, with actual data beginning in row 2.
  • LargerSheet must be sorted both by Name (Ascending) and Date (Ascending), with sorting priority set in that order.

Another thing to be aware of is a distinction between "within 2 days" and "within 48 hours" - this formula uses the latter. That is, if a time is 5/6/2012 03:00:00 on Sheet1 and the corresponding value on LargerSheet is 5/8/2012 03:00:01 then the IF statement will evaluate to FALSE and return the value from Sheet1 instead of the one from LargerSheet. Substantial formula modifications would be needed to account for if you want to match "any future time over the next two days" as opposed to "any time within 48 hours".

Also, because the IF statement uses an exclusive greater-than (>) instead of a greater-than-or-equal-to, times which match exactly will cause it to evaluate to FALSE. If you want it to evaluate TRUE for exact matches, replace > with >=.

Improve this answer
6
  • I take note that you have laid a substantial amount of work on this -:-). General comments for the future: From the above one can understand that a lot of detail is required when the complexity of the question gets beyond a certain level. At this raised level - it isn't easy to provide useful advice through a channel similar to e.g. Super User. This is a task more suitable for a hired consultant.
    – Hannu
    Jul 31, 2014 at 7:48
  • @Hannu Oh, it's easy to provide useful advice for stuff like this. However, a few things need to happen which didn't quite go right here: 1.) The Asker needs to provide clear and specific details about how their sheet is laid out, and everything they're hoping to achieve with the component in question. 2.) The Asker needs to properly follow instructions and examples provided by Answerers. OR 1.) The Asker should be knowledgeable and capable enough to recognize, and make changes, when formulas provided by Answerers need to be adjusted to match their actual spreadsheet layout or intent.
    – Iszi
    Jul 31, 2014 at 14:42
  • In short, this all could have been answered with one Q&A instead of two or three if the question itself was properly formulated and detailed and the had properly followed instructions. Plus, it helps when they are able to do at least minor pieces of the work themselves.
    – Iszi
    Jul 31, 2014 at 14:43
  • @Iszi I would agree that I should have asked the question more precisely. There is a small issue with your solution still, though. The problem in the original Q&A was that the function would always be checking against the most recent date, and it seems that your function also does this. When evaluating the second condition in AND() this means that it will find that, for example, 05/08/2014 is not less than 05/01/2014 and evaluate to false even if the same entity has a date 05/02/2014 that would satisfy the condition. To be sure, I appreciate all of your help here- I've learned a lot!
    – 114
    Jul 31, 2014 at 16:48
  • @114 Your current question is also unclear on this point. You say "I realized, however, that the true problem is not to have the most recent date returned if the condition is met, but to have the date returned if it is greater than and within 2 days ..." the phrasing implies the most recent date from LargerSheet is still fine, but needs to be disqualified if it is in excess of two days after the reference date on Sheet1. If your matching criteria really needs to be "any date on LargerSheet that is within two days after the reference date on Sheet1", the formula needs more significant work.
    – Iszi
    Jul 31, 2014 at 17:10
2

NOTE HERE: I'm just viewing your IF() and changing the condition-statement.
If this does not work for you - it might at least give an idea of how to write/change it.

=IF(
     AND(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"&        //Cell starting with "$B" and ending with
     MATCH($A1,LargerSheet!$A:$A,1)    //row of the last date for the name.
     )<($B1+2)),                       //Compare with SmallerSheet (date-2)
     INDIRECT("LargerSheet!$B"&        //"Then" return LargerSheet date, 
     MATCH($A1,LargerSheet!$A:$A,1)
     ),$B1)

... the important addition is AND( condition1, condition2 ) between IF( and the first , for the IF.

Improve this answer
15
  • Darn, you beat me to it. However, I think the OP is looking for the second condition to be >($B1+2) not -2.
    – Iszi
    Jul 30, 2014 at 17:52
  • =) I had the beating be the other way yesterday ;-) -- and you MAY WELL be right about the condition. I'm not sure, I'll let it be as is for now. Your opinion is visible and may solve it for OP.
    – Hannu
    Jul 30, 2014 at 17:54
  • @Iszi I think it should also be <(B1+2), no? I am looking for it to be less than two days after the LargerSheet date.
    – 114
    Jul 30, 2014 at 19:01
  • Right, I'll edit/change it - it is all about my initial reading of your explanation. Have you tried it? :-)
    – Hannu
    Jul 30, 2014 at 19:06
  • @Hannu I still need to try it, but I'm skeptical because I think it's the same in spirit as the function I added to my post, which didn't work. I shouldn't be so negative though, I should just try it!
    – 114
    Jul 30, 2014 at 19:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .