• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup data between two date ranges

Lee Churchwell

New Member
Hi, all. I'm trying to populate a column with a "pickup date" for a given ID# based on a delivery date. This delivery date falls between a range of dates on a tab "TF" and is based on two factors: 1.) supplier ID and 2.)PO deliver date.

I've tried INDEX formulas, MIN, and MATCH but cannot find success. Any ideas?
 

Attachments

  • Sample file.xlsx
    357.5 KB · Views: 6
Welcome to the Forum

Try the below in D2 =INDEX(TF!$B$2:$B$245,MATCH(1,(TF!$C$2:$C$245<=$C2)*(TF!$D$2:$D$245>=$C2)*(TF!$A$2:$A$245=$A2),0))

and drag it down..

Acknowledge with CTRL + SHIFT + ENTER

If otherwise, please share the sample output as well..
 
Hi Lee -

I've taken the liberty of including Asheesh's formula in my worksheet, but used Excel's Table capability as I find it's easier to follow formulas when they're in the Structures Reference format that the feature uses ...

As you will see Asheesh's formula produces more #N/A than answers that *appear* correct. That is not to denigrate his effort in any way, it's just that as he requested, if you provide the date you expect to return in the final column on Sheet1, and possible some more detail in the decision making criteria, we'll have you an answer in no time .... Just do it for the first half dozen or so rows and more detail about how you derive the answer is always better than too little ...:DD
 

Attachments

  • Lee C - Shipping Challenge.xlsx
    386.2 KB · Views: 8
Another option,

In D2, formula :

=SUMIFS(TF!B:B,TF!C:C,"<="&$C2,TF!D:D,">="&$C2,TF!A:A,$A2)

and D2 >> Custom Cell Formatting >> enter : d-mmm-yy;;;

All copy down

Regards
 
Another option in using Index+Match and in normal formula :

In D2, copy down :

=IFERROR(INDEX(TF!$B$2:$B$245,MATCH(1,INDEX((TF!$C$2:$C$245<=$C2)*(TF!$D$2:$D$245>=$C2)*(TF!$A$2:$A$245=$A2),0),0)),"")

Regards
 
Thank you all for your assistance and sorry for delayed reply!

My intent is to analyze the PO delivery date in "Sheet1" to see if it falls in the date range of columns C & D in sheet "TF". If the date falls in the range, I want to return the value "Pickup Date" in sheet "TF".

It looks like I have some outlyer data (i.e. PO deliveries outside the current scheduled pickups). I've pared the attached file down to a few examples.
 

Attachments

  • Lee C - Shipping Challenge.xlsx
    320.8 KB · Views: 6
Back
Top