lookup with multiple conditions and date range

[gsabbella]

Hi Team, please help me to get the data based on below requirement. 

Please help to me with relevant formula/looks. 

Discount% required in the Product Purchase Table 1 where Purchase date in table 1  falls between Season Start Date and Season Start Date in Table 2 with product match.

Product Purchase Table 1:
Product	Product Purchase Date	Discount
A	5-Jan-21	10%
A	1-Sep-22	?
A	7-Jul-21	?
B	21-Oct-21	?
B	12-Dec-21	?
B	15-Jun-22	?

Discount Table 2
Product	Season Start Date	Season End Date	Discount
A	1-Jan-21	31-Mar-21	10%
B	1-Apr-21	30-Jun-21	20%
A	1-Jul-21	30-Sep-21	30%
B	1-Oct-21	31-Dec-21	40%

[#2]

@gsabbella, Try using the following,
=IFERROR(FILTER(IF(C4=$C$14:$C$17,IF(D4>=$D$14:$D$17,IF(D4<$E$14:$E$17,$F$14:$F$17,""),""),""),IF(C4=$C$14:$C$17,IF(D4>=$D$14:$D$17,IF(D4<$E$14:$E$17,$F$14:$F$17,""),""),"")<>""),"")

Attached file for your reference. but you need to have the discount for the date in table 2, else you will get a blank cell.

[#3]

@gsabbella 

=IFERROR(INDEX($D$12:$D$15,MATCH(1,(A3=$A$12:$A$15)*(B3>=$B$12:$B$15)*(B3<=$C$12:$C$15),0)),"")

If you don't have access to the FILTER function you can try INDEX and MATCH. Enter this formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.