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

 

[etaf]

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

as we we cannot see the file - its difficult to follow
perhaps share on onedrive or dropbox
or explain in more details what the columns actually are titled and have content

so that if "F" shows up anywhere in "A"), and "G" falls between "B"-"C", the correct value will be returned.

rather than guess perhaps a detail reply

you maybe able to use a countifs()
using the vendor ID number and the dates

does the rebate file have
item number in a column
Start date for rebat in another column
End date in another column

then countifs(vendor rebat list range , vendor number on inventory , vendor rebat list Start date range ">="& Date purchased , vendor rebat list End date range "<="& Date purchased )
if that =1
then the vendor and date exists in the file
so you know a rebat is due

 

[miken54]

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

Thanks so much for the reply! I hope the below information shows gives enough detail.
A – D is my vendors report. F-G is my companies report.
A: The item # that my vendor lists
B: The vendors START date for a certain program
C: The vendors END date for that program
D: The rebate $$ amount offered

My companies report shows
F: The item # that my company purchased
G: The date in which we purchased it

Ultimately, I am looking for a formula to enter into H3 (and then all of H) that would lookup if F3 shows up anywhere in column A. If it does, then if G3 (the date I purchased it) falls on or between B and C in that same row, then the value listed in D would be returned in H3.

So in this example pictured above, F3 (model 1234) does indeed show up in A (A3), AND G3 (the date of 1/2/2017) falls on or between B2 and C2, so the value in D2 ($200), is returned to H3.

The major problem I can’t figure out is when there are multiple programs for the same item # in A, just with different dates ranges. I don’t know which formula to use.

Hopefully I was able to explain this well enough.

 

[etaf]

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

so ABCD are not connected with F and G at all on the row
so row 3 can have a data for vendor A-D and the data on F -G row 3 is unrelated to the A-D row 3 data

this should work in H2 and copy down

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

will return an error if the ID and dates are not found
and so you can use IFERROR and return a 0 if not found

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

 

[etaf]

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

Excel Workbook
	A	B	C	D	E	F	G	H
1	Vendor ID	Start	End	rebate		ID	Date purchased	Rebate Due
2	1	01/05/2016	11/05/2016	10		3	10/07/2016	0
3	1	01/01/2017	11/01/2017	20		4	11/07/2016	0
4	2	01/06/2016	11/06/2016	30		5	12/07/2016	0
5	2	01/02/2017	11/02/2017	40		6	10/10/2016	110
6	3	01/07/2016	09/07/2016	50		7	08/10/2016	0
7	3	01/03/2017	11/03/2017	60		8	08/12/2016	150
8	4	01/08/2016	11/08/2016	70		9	06/03/2017	180
9	4	01/04/2017	11/04/2017	80		9	07/03/2017	180
10	5	01/09/2016	11/09/2016	90		9	08/03/2017	180
11	5	01/05/2017	11/05/2017	100		9	09/03/2017	180
12	6	01/10/2016	11/10/2016	110		13	01/01/2017	260
13	6	01/06/2017	11/06/2017	120		9	02/01/2017	170
14	7	01/11/2016	11/11/2016	130		12	03/01/2017	0
15	7	01/01/2017	11/01/2017	140		13	04/01/2017	260
16	8	01/12/2016	11/12/2016	150		17	05/01/2017	0
17	8	01/02/2017	11/02/2017	160		18	25/07/2016	0
18	9	01/01/2017	11/01/2017	170		19	26/07/2016	0
19	9	01/03/2017	11/03/2017	180		20	27/07/2016	0
20	10	01/02/2017	11/02/2017	190
21	10	01/04/2017	11/04/2017	200
22	11	01/07/2016	11/07/2016	210
23	11	01/05/2017	11/05/2017	220
24	12	01/08/2016	11/08/2016	230
25	12	01/06/2017	11/06/2017	240
26	13	01/09/2016	11/09/2016	250
27	13	01/01/2017	11/01/2017	260
28	14	01/10/2016	11/10/2016	270
29	14	01/02/2017	11/02/2017	280
30	15	01/11/2016	11/11/2016	290
31	15	01/03/2017	11/03/2017	300
32	16	01/12/2016	11/12/2016	310
33	16	01/04/2017	11/04/2017	320
Sheet1

 

[miken54]

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

First off, thank you so much for taking the time out of your day to assist me.

You are probably going to roll your eyes with this question, but can you possibly explain why "1E+307,1/" is part of this formula and what it does? I think I am getting confused because "E" is blank

 

[etaf]

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

E is not a cell reference is a raised to power of
https://www.mrexcel.com/forum/excel-questions/567072-what-does-1e-307-mean.html

 

[miken54]

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

Understood. Again, thank you so so so much.

 

[miken54]

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

Understood. Again, thank you so so so much.

Hi ETAF,

I was wondering if I could pick your brain again. The information you gave me has been working so great (thanks again!), and there is a new project that is very similar to the first one, I just dont want to screw up the formula. In this project, there are more rebates we can earn if both the date ordered (G) and the date received (H) fall on or between the start (B) and end (C) dates. If you had any idea as to which additions to the formula you previously listed, it would be such a huge help. I hope this table actually shows up this time (not sure what I did wrong the first time haha) Thanks so much!

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

A	B	C	D	E	F	G	H

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="align: center"]Vendor ID[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]End[/TD]
[TD="align: center"]rebate[/TD]

[TD="align: center"]ID[/TD]
[TD="align: center"]Date purchased[/TD]
[TD="align: center"]Rebate Due[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]01/05/2016[/TD]
[TD="align: center"]11/05/2016[/TD]
[TD="align: center"]10[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]10/07/2016[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]01/01/2017[/TD]
[TD="align: center"]11/01/2017[/TD]
[TD="align: center"]20[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]11/07/2016[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]01/06/2016[/TD]
[TD="align: center"]11/06/2016[/TD]
[TD="align: center"]30[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]12/07/2016[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]01/02/2017[/TD]
[TD="align: center"]11/02/2017[/TD]
[TD="align: center"]40[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]10/10/2016[/TD]
[TD="align: center"]110[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]01/07/2016[/TD]
[TD="align: center"]09/07/2016[/TD]
[TD="align: center"]50[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]08/10/2016[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]01/03/2017[/TD]
[TD="align: center"]11/03/2017[/TD]
[TD="align: center"]60[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]08/12/2016[/TD]
[TD="align: center"]150[/TD]

</tbody>

</body>[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]
[TD]J[/TD]
[/TR]
[TR]
[TD]VENDOR ID[/TD]
[TD]START[/TD]
[TD]END[/TD]
[TD]REBATE[/TD]
[TD][/TD]
[TD]ID[/TD]
[TD]DATE ORDERED[/TD]
[TD]DATE REC'D[/TD]
[TD]REBATE DUE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/5/2016[/TD]
[TD]1/19/2016[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1/6/2016[/TD]
[TD]1/18/2016[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/21/2016[/TD]
[TD]1/23/2016[/TD]
[TD]75[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1/2/2016[/TD]
[TD]1/7/2016[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/1/2016[/TD]
[TD]1/30/2016[/TD]
[TD]15[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]1/1/2016[/TD]
[TD]2/1/2016[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[etaf]

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))