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

miken54

New Member
Joined
Sep 18, 2017
Messages
20
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?

0



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

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
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

0


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.
 
Upvote 0
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)
 
Last edited:
Upvote 0
Re: Formula Help Request. IF one date falls on/between 2 others AND a cell matches, then return result

Excel Workbook
ABCDEFGH
1Vendor IDStartEndrebateIDDate purchasedRebate Due
2101/05/201611/05/201610310/07/20160
3101/01/201711/01/201720411/07/20160
4201/06/201611/06/201630512/07/20160
5201/02/201711/02/201740610/10/2016110
6301/07/201609/07/201650708/10/20160
7301/03/201711/03/201760808/12/2016150
8401/08/201611/08/201670906/03/2017180
9401/04/201711/04/201780907/03/2017180
10501/09/201611/09/201690908/03/2017180
11501/05/201711/05/2017100909/03/2017180
12601/10/201611/10/20161101301/01/2017260
13601/06/201711/06/2017120902/01/2017170
14701/11/201611/11/20161301203/01/20170
15701/01/201711/01/20171401304/01/2017260
16801/12/201611/12/20161501705/01/20170
17801/02/201711/02/20171601825/07/20160
18901/01/201711/01/20171701926/07/20160
19901/03/201711/03/20171802027/07/20160
201001/02/201711/02/2017190
211001/04/201711/04/2017200
221101/07/201611/07/2016210
231101/05/201711/05/2017220
241201/08/201611/08/2016230
251201/06/201711/06/2017240
261301/09/201611/09/2016250
271301/01/201711/01/2017260
281401/10/201611/10/2016270
291401/02/201711/02/2017280
301501/11/201611/11/2016290
311501/03/201711/03/2017300
321601/12/201611/12/2016310
331601/04/201711/04/2017320
Sheet1
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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;">

<tbody>
</tbody>
ABCDEFGH
1Vendor IDStartEndrebateIDDate purchasedRebate Due
2101/05/201611/05/201610310/07/20160
3101/01/201711/01/201720411/07/20160
4201/06/201611/06/201630512/07/20160
5201/02/201711/02/201740610/10/2016110
6301/07/201609/07/201650708/10/20160
7301/03/201711/03/201760808/12/2016150

<tbody>
</tbody>
</body>
ABCDEFGHIJ
VENDOR IDSTARTENDREBATEIDDATE ORDEREDDATE REC'DREBATE DUE
11/5/20161/19/20161011/6/20161/18/201610
11/21/20161/23/20167511/2/20161/7/20160
21/1/20161/30/20161521/1/20162/1/20160

<tbody>
</tbody>
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top