IF one date falls on/between 2 others AND a cell matches, then return result

[miken54]

Hi Experts, I would really appreciate some guidance as I cant quite figure out what formula to use. If this was already answered previously, I cant seem to find. I Already spent hours on forums and youtube videos. Perhaps I am wording my question wrong? ​

I have thousands of rows of ITEM #'s that my company buys each month from a certain vendor. At any point in a given month, there can be rebates that we are entitled to claim. My vendor lists each item#, and if we purchase that item on or between the date range they list, we earn a certain rebate.


So in the example below, model 5555 has three different rebates happening in the month of January, varying in amounts that are due to my company. In the last line of my Vendors Report, you will see that between 1/21/17-1/23/17; there are no rebates.


My companies report below lists each model we purchased and when we purchased it.


I'm losing my mind trying to determine what formula to use so into "H" so that if "F" shows up anywhere in "A"), and "G" falls between "B"-"C", the correct value will be returned.


Would it be a combination of V-Lookup/ if/and?

​I hope I worded this correctly. Any suggestions would be greatly appreciated. ​

 

[miken54]

Re: Formula Help Request. IF one date falls on/between 2 others AND a cell matches, then return result

try
=IFERROR(LOOKUP(1E+307,1/((F2=A:A)*(G2>=B:B)*(G2<=C:C))*D:D),IFERROR(LOOKUP(1E+307,1/((F2=A:A)*(H2>=B:B)*(H2<=C:C))*D:D),0))

Thank you so much!!!!!

 

[miken54]

Re: Formula Help Request. IF one date falls on/between 2 others AND a cell matches, then return result

try
=IFERROR(LOOKUP(1E+307,1/((F2=A:A)*(G2>=B:B)*(G2<=C:C))*D:D),IFERROR(LOOKUP(1E+307,1/((F2=A:A)*(H2>=B:B)*(H2<=C:C))*D:D),0))

Hey, I feel like its super close, just missing something small (most likely because of the way I tried to explain it). In cell i4, when I run this formula, I am getting the returned value of $106, however it should be $0. G4 (the "ordered date of
10/23/2017) should not qualify for a rebate because it was ordered on a date that the promotional period (B) was not offering any rebates.

In order to qualify for a rebate, both G and H must fall on or between B and C.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]ITEM[/TD]
[TD]START DATE[/TD]
[TD]END DATE[/TD]
[TD]REBATE[/TD]
[TD][/TD]
[TD]ITEM[/TD]
[TD]ORDERED[/TD]
[TD]REC'D[/TD]
[TD]REWARD[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9/27/2017[/TD]
[TD]10/18/2017[/TD]
[TD]65[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]9/28/2017[/TD]
[TD]10/1/2017[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11/1/2017[/TD]
[TD]11/29/2017[/TD]
[TD]106[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]10/23/2017[/TD]
[TD]11/1/2017[/TD]
[TD]106[/TD]
[/TR]
</tbody>[/TABLE]


You have been so helpful so far, I just spent the last 2 hours trying to do this on my own but I cant figure it out. If you wouldnt mind, it would be such a huge help. Thanks again

 

[etaf]

Re: Formula Help Request. IF one date falls on/between 2 others AND a cell matches, then return result

=IFERROR(LOOKUP(1E+307,1/((F2=A:A)*(G2>=B:B)*(H2>=B:B)*(H2<=C:C)*(G2<=C:C))*D:D),0)

so this is using
(G2>=B:B)*(H2>=B:B)*(H2<=C:C)*(G2<=C:C)

So both G and H must be between B and C

 

[miken54]

Re: Formula Help Request. IF one date falls on/between 2 others AND a cell matches, then return result

=IFERROR(LOOKUP(1E+307,1/((F2=A:A)*(G2>=B:B)*(H2>=B:B)*(H2<=C:C)*(G2<=C:C))*D:D),0)

so this is using
(G2>=B:B)*(H2>=B:B)*(H2<=C:C)*(G2<=C:C)

So both G and H must be between B and C

OK, you are AMAZING!!!! This worked perfectly and I am able to go back and confirm every old rebate! Thank you so much. Happy Holidays to you and your family!!!!

 

[etaf]

Re: Formula Help Request. IF one date falls on/between 2 others AND a cell matches, then return result

you are welcome
have a great holiday and new year