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?




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

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,562
Office Version
365
Platform
MacOS
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

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

Well-known Member
Joined
Oct 24, 2012
Messages
3,562
Office Version
365
Platform
MacOS
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:

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,562
Office Version
365
Platform
MacOS
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
 

miken54

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

miken54

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

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

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,562
Office Version
365
Platform
MacOS
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))
 

Forum statistics

Threads
1,089,437
Messages
5,408,214
Members
403,190
Latest member
RBrite

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top