formulas to search more than one worksheet

kdwork 

You'd simplify things a lot if you copied/pasted the contents of the Product List ITL_BULK sheet below the contents of the Product List-DOMESTIC sheet.

If that is not feasible, you can change the formula in J2 to

=IF(OR($E$3={"CAN.INTL","BULK"}),IFERROR(OFFSET('Product List ITL_Bulk'!$A$1,MATCH(1,INDEX(($E$3='Product List ITL_Bulk'!$A$2:$A$771)*($C$6='Product List ITL_Bulk'!$D$2:$D$771),,),0),MATCH($I$2,'Product List ITL_Bulk'!$A$1:$CB$1,0)-1,1,1),"-"),IFERROR(OFFSET('Product List-DOMESTIC'!$A$1,MATCH(1,INDEX(($E$3='Product List-DOMESTIC'!$A$2:$A$771)*($C$6='Product List-DOMESTIC'!$D$2:$D$771),,),0),MATCH($I$2,'Product List-DOMESTIC'!$A$1:$CB$1,0)-1,1,1),"-"))

and similar for the rest...