How to analyze multiple conditions in a row, return an answer in the same row, new column

Have look at the attached:

I have used the following as a basic calculation:

=SUMPRODUCT((Sheet1!$D$4:$D$38=$A6)*(Sheet1!$E$4:$G$38)*(Sheet1!$E$3:$G$3=$B6))+SUMPRODUCT((Sheet1!$D$4:$D$38=$A6)*((Sheet1!$H$3:$H$3=$B6)*(Sheet1!$E$4:$H$38)*$C6))

I have effectively combined two lookups: one for the flat rate commission (first SUMPRODUCT) and "added" the "appraisal" % calculation ( second SUMPRODUCT): it will be one or the other

Alternatives could include testing if it as an "Appraisal" and separating the calculations:

=IF (Appraisal, SUMPRODUCT #2, SUMPRODUCT #1)

In the above SUMPRODUCT #2 could be replaced with a VLOOKUP

=VLOOKUP(A6,Sheet1!D4:H38,5,0)*C6