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

Look Up with multiple conditions with two sets of date ranges

E

emo129

New Member
I have 8000 line file that I am trying to identify a particular discount amount against another file of about 3000 lines. I have to match an item # plus a start and end date with the other file within a date range and bring back a discount/dollar amount. The table will look like this.


File Needed To Populate

[pre]
Code: 
Items	Start Date	End Date	Discount Amount
Item 1	7/8/12	        7/14/12	        ?
Item 1	7/22/12	        7/28/12	        ?
Item 1	7/29/13	        8/4/12	        ?
Item 2	7/8/12	        7/14/12	        ?
Item 2	7/22/12	        7/28/12	        ?
Item 2	7/29/13	        8/4/12	        ?
Item 2	8/5/12	        8/11/12	        ?
Item 3	7/29/13	        8/4/12	        ?
Item 3	8/5/12	        8/11/12	        ?
Item 4	7/8/12	        7/14/12	        ?
Item 4	7/22/12	        7/28/12	        ?
Item 4	7/29/13	        8/4/12	        ?
Item 5	7/22/12	        7/28/12	        ?
Item 6	7/8/12	        7/14/12	        ?
Item 6	7/22/12	        7/28/12	        ?
Item 6	7/29/13	        8/4/12	        ?
Item 6	8/5/12	        8/11/12	        ?
===============================================================================

Data Table

Items	Start Date End Date   Discount Amount
Item 1	6/24/12	   7/1/12        $2.75
Item 1	7/2/12	   8/4/12        $2.75
Item 1	9/2/12	   9/30/12	 $2.75
Item 2	10/1/12	   10/27/12	 $2.75
Item 2	11/22/12   1/5/13	 $4.44
Item 2	7/2/12	   7/30/12	 $2.75
Item 2	8/1/12	   8/27/12	 $3.50
Item 2	11/22/12   1/5/13	 $4.44
Item 2	10/14/12   11/17/12	 $2.50
Item 3	12/2/12	   12/29/12	 $2.75
Item 3	12/30/12   3/2/13	 $2.75
Item 3	7/4/12	   7/29/12	 $5.50
Item 3	9/2/12	   9/30/12	 $2.75
Item 3	8/1/12	   8/27/12	 $2.75
Item 3	10/28/12   12/29/12	 $2.75
Item 4	7/1/12	   7/15/12	 $2.50
Item 4	7/16/12	   8/1/12	 $2.75
Item 4	10/1/12	   10/27/12	 $2.50
Item 4	10/28/12   12/29/12	 $2.75
Item 4	7/8/12	   9/8/12	 $2.75
Item 4	10/14/12   11/17/12	 $2.50
Item 4	12/2/12	   12/29/12	 $2.75
Item 4	10/14/12   11/17/12	 $2.50
Item 4	10/14/12   11/17/12	 $2.50
Item 5	10/14/12   11/17/12	 $2.50
Item 5	7/8/12	   9/8/12	 $2.75
Item 5	10/14/12   11/17/12	 $2.50
Item 5	7/8/12	   9/8/12	 $2.75
Item 5	6/24/12	   7/1/12	 $6.75
Item 5	7/2/12	   8/4/12	 $2.75
Item 6	10/14/12   11/17/12	 $2.50
Item 6	10/28/12   11/17/12	 $2.75
Item 6	7/2/12	   8/4/12	 $4.44
Item 6	6/24/12	   7/1/12	 $2.75
[/pre]
Thanks!
 
i went through your list manually and want to make sure I'm correct:


Items------Start--------End----------manual

Item 1-----7/08/2013----7/14/2013----$2.75

Item 1-----7/22/2013----7/28/2013----$2.75

Item 1-----7/29/2013----8/04/2013----$2.75

Item 2-----7/08/2013----7/14/2013----$2.75

Item 2-----7/22/2013----7/28/2013----$2.75

Item 2-----7/29/2013----8/04/2013----$3.50

Item 2-----8/05/2013----8/11/2013----$3.50

Item 3-----7/29/2013----8/04/2013----$2.75

Item 3-----8/05/2013----8/11/2013----$2.75

Item 4-----7/08/2013----7/14/2013----$2.50

Item 4-----7/22/2013----7/28/2013----$2.75

Item 4-----7/29/2013----8/04/2013----$2.75??

Item 5-----7/22/2013----7/28/2013----$2.75

Item 6-----7/08/2013----7/14/2013----$4.44

Item 6-----7/22/2013----7/28/2013----$4.44

Item 6-----7/29/2013----8/04/2013----$4.44

Item 6-----8/05/2013----8/11/2013----??


There are 2 that I am personally questioning.
 
Hi Emo


Please name the columns in the Data Table as follows using the Name Manager.


Items, Start_Date, End_Date and Discount_Amount


Put the following formula in the File Needed To Populate.


=sumproduct(--(Items=A2),--(Start_Date=B2),--(End_Date=C2),Discount_Amount)


The data has been assumed to start from cell A1 in File Needed To Populate.


Amritansh
 
Tweaking amrit's formula a little, since it's possible for lookup date to fall between the ranges.

=sumproduct(--(Items=A2),--(Start_Date<=B2),--(End_Date>=C2),Discount_Amount)
 
Jason - For Item 4 your result is correct it would be $2.75. For Item 6 it would return back an error or no results since it would not meet the conditions.


Amrit & Luke - Thanks for the input. I need for it to look in the total population in the "Data Table", not an individual line item. Can I do it with a range such as B2:B20 or something to that nature?
 
Sure, I think that's what we suggesting. Just used names instead of ranges. Not sure where you data table is in relation to lookup data, but something like this:

=SUMPRODUCT(--(Sheet2!A$2:A$20=A2),--(Sheet2!B$2:B$20<=B2),--(Sheet2!C$2:C$20>=C2),Sheet2!D$2:D$20)
 
Luke - Thank You worked like a charm! I do have a small issue which is mainly due to the information on my data table. There are some items that have results that overlap in the timing of it. Is there a way to bring back the highest discount amount rather than summing the values? I know that is due to the "sumproduct" function, but if there is a way to bring back the highest value would be perfect! Thanks again for your quick responses!
 
Hi Emo


Try the modified version of formula from Luke.


= MAX(--(Sheet2!A$2:A$20=A2)*--(Sheet2!B$2:B$20<=B2)*--(Sheet2!C$2:C$20>=C2)*(Sheet2!D$2:D$20))


Amritansh
 
=SUMPRODUCT(MAX((Sheet2!A$2:A$20=A2)*(Sheet2!B$2:B$20<=B2)*(Sheet2!C$2:C$20>=C2)*(Sheet2!D$2:D$20)))
 
Thanks Amritansh but I was nto able to get any results.


Thanks Luke this one worked.


Thanks Everyone for all your input!
 
Hi Luke


I am unable to understand what I did wrong :eek:


Since MAX will return the single value, why have you used the SUMPRODUCT?


Amritansh
 
Hi Amrit,

I think your formula would have worked if you confirm it as an array. By using SUMPRODUCT, XL automaitcally knew I was using an array, so you can confirm it as a regular formula. Mine's a little longer to write, but array formula sometimes confuse people, so I chose that route.


Also, you don't need to have the "--" in your formula if you multiply the arrays. The "--" is just there to force the text strings of true/false into 1 and 0. But, multiplying the arrays already forces them into number form, so it's not necessary.
 
You must log in or register to reply here.
Back
Top