Need help creating a formula to check a date range

Try in N8 of TEST

=IF(COUNTIFS(Deals!G:G,Test!$C$1,Deals!N:N,Test!$A8,Deals!E:E,"<=" &Test!$C$6,Deals!F:F,">=" &Test!$C$6),"Deal Active","")

Originally Posted by ChemistB

In N8 copied down

=IFERROR(IF(AND(Test!$C$6>= INDEX(Deals!$E$2:$E$1000, MATCH(Test!$C$1, Deals!$G$2:$G$1000,0)), Test!$C$6<= INDEX(Deals!$F$2:$F$1000, MATCH(Test!$C$1, Deals!$G$2:$G$1000,0))), "Valid", "Not Valid"), "")

Assumptions:
1. All of your formatting on Deals and Test are the same. They are not. I converted Deals!Column G to Text to match Test!C1 and Changed Test!C6 from text to Date to match columns E and F on Deals!

2. You only have 1 deal per "shipped to" on the Deals sheet. Is this true? If not, will you have multiple deals with the same Material or one per material? What critieria would you use to determine Valid vs Invalid?

Thank you for the quick response. There will be other sales deals that have matching material numbers but some might have matching dates but will have different ship to's. I updated the spreadsheet with a little more data. Would you be able to adjust the formula to work with the new sales deals I added to Ship To 12345? Thank you for your help!

Originally Posted by JohnTopley

Try in N8 of TEST

=IF(COUNTIFS(Deals!G:G,Test!$C$1,Deals!N:N,Test!$A8,Deals!E:E,"<=" &Test!$C$6,Deals!F:F,">=" &Test!$C$6),"Deal Active","")

Thank you for the quick response is there a way to show if the deal is inactive but leave the material that does not have a deal blank so if you look at the above spreadsheet I just attached material 280138 does not have a sales deal at all would remain blank but still show the others that have sales deals as active or inactive?

I guess I am leaving out the fact that I will eventually want to hide the base price, tier price, and sales deal column so the only thing that can be seen is the best price as well as sales deal active.