Excel formula - How to conditionally fill a column with values from another table

user_13148253 0
Viewed 397

I have 2 tables here:

enter image description here

I want to fill up the Code column in Table 1 by referring to Table 2. The condition for the value is that the Start date must be in between the ProductionDate and ExpiryDate of Table 2, and the Type in Table 1 must match the Type in Table 2, else return NA. I have come up with the formula for cell D4

=IFS(AND(C4>=$H$4,C4<=$I$4,B4=$G$4),$F$4,AND(C4>=$H$5,C4<=$I$5,B4=$G$5),$F$5,AND(C4>=$H$6,C4<=$I$6,B4=$G$6),$F$6)

Is there a simpler way to write the formula?

conditional-statements if-statement excel-formula excel
2 Answers

You can try FILTER() function.

=FILTER($F$4:$F$6,($H$4:$H$6<C4)*($I$4:$I$6>C4)*($G$4:$G$6=B4),"NA")

enter image description here

user_55147470

I have found another way to write the formula:

By naming the range F4:F6 as code, G4:G6 as type, H4:H6 as prod_date, and I4:I6 as exp_date, we can use the IFERROR() and MATCH() function as below.

=IFERROR(MATCH(1,(B4=type) * (C4>=prod_date) * (C4<=exp_date),0),"NA")

user_131482530
Related