SUMPRODUCT with embedded MAX/MIN

Hi Folks -- good evening!

I have a spreadsheet with two tabs. Tab 1: *Job Listing* contains job title, Pay Scale Code and an area to pull in the minimum and maximum hourly wages from Tab 2: *Wage Rates.*

My SUMPRODUCT formula was working until I realized that not all Pay Scales have the same number of pay steps. I need a formula that will allow me to find the minimum or maximum step value and return the correct pay amount for that step.

So instead of (1='Wage Rates'!$B$2:$B$31) below, I need to take the MAX or MIN of the pay steps for that particular pay scale code and return the correct pay value.

=SUMPRODUCT((C2='Wage Rates'!$A$2:$A$31)*(1='Wage Rates'!$B$2:$B$31)*'Wage Rates'!$E$2:$E$31)

Sample Spreadsheet 3.xls

SUMPRODUCT would be my preference but if it isn't a possibility can you let me know why? Still trying to understand this amazing function.

My sample spreadsheet is attached. Hoping there is a way to do this. Thanks!