IF/OR INDEX MATCH STATEMENT

kdwork  so you say it doesn't work but it is working exactly as you designed it in that it finds the corresponding row and col and offsets to the corresponding cell.  That cell happens to be blank which it takes as 0.  That is not an error and hence doesn't use that RED portion of the formula.  If you really want that to happen a simple trick is to add 1/1/ in front of it to force a divide by 0 error:

=IF(OR($E$3={"CAN.INTL","BULK"}),IFERROR(1/(1/Value(OFFSET('ITL_Bulk Varieties'!$A$1,MATCH(1,INDEX(($E$3='ITL_Bulk Varieties'!$A$2:$A$654)*($C$6='ITL_Bulk Varieties'!$E$2:$E$654),,),0),MATCH($S$9,'ITL_Bulk Varieties'!$A$1:$CC$1,0)-1,1,1))),100-R10-R11-R12),IFERROR(OFFSET('DOMESTIC VARIETIES'!$A$1,MATCH(1,INDEX(($E$3='DOMESTIC VARIETIES'!$A$2:$A$600)*($C$6='DOMESTIC VARIETIES'!$E$2:$E$600),,),0),MATCH($S$9,'DOMESTIC VARIETIES'!$A$1:$CP$1,0)-1,1,1),100-R10-R11-R12))

that said I suspect you might need the same for the second half of the formula even though you say it is working.

That all said, you could also make the whole thing cleaner/more simple using the new LET statement 

=LET(tab,IF(OR($E$3={"CAN.INTL","BULK"}),'ITL_Bulk Varieties'!$A$1:$CC$652,'DOMESTIC VARIETIES'!$A$1:$CP$600),
        brandCol, INDEX(tab,,1),
        varietyCol, INDEX(tab,,5),
        headerRow, INDEX(tab,1,),
        irow, MATCH(1,($E$3=brandCol)*($C$6=varietyCol),0),
        icol, MATCH($S$9,headerRow,0),
        foundCell, INDEX(tab,irow,icol),
        altResult, 100-R10-R11-R12,
        IFERROR(IF(foundCell=0,altResult,foundCell),altResult)
        )

I put that version in col F just to the right of the original