# lookup with multiple conditions and date range

Copper Contributor

# lookup with multiple conditions and date range

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 Replies

# Re: lookup with multiple conditions and date range

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

# Re: lookup with multiple conditions and date range

``=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.