Easier Way for IF statement and payroll formula

[mauirlesette]

My control value is D46. My data is A7 to A141 for the min number and B7 to B141 for max, and C7 to c141 for the amount. I used the following formula and it works perfectly. I need to include all the data down to row 141. Is there an easy way to include all the values from rows 7-141? (other than typing each statement manually?)

=IF(AND(D46>Sheet3!A7,Jan!D46<=Sheet3!B7),Sheet3!C7)+IF(AND(D46>Sheet3!A8,Jan!D46<=Sheet3!B8),Sheet3!C8)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]at least[/TD]
[TD]but less than[/TD]
[TD]equals[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]115[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD]120[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]125[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[ExcelGuyDude]

I would use a SUMPRODUCT function...

=INDEX(Sheet3!$C:$C,SUMPRODUCT(--(D46>Sheet3!$A$7:$A$141),--(D46<=Sheet3!$B$7:$B$141),ROW(Sheet3!$A$7:$A$141)))

 

[svendiamond]

=vlookup(d46,a:c,3,true)

 

[mauirlesette]

Thank you!!!!!

 

[sheetspread]

If that pattern continues you can do without the lookup table:

Excel 2010
	D	E
2	114	0
Sheet3 (2)

Cell Formulas
Range	Formula
E2	=IF(D2<115,0,(1+(FLOOR(D2,5)-110)/5))

or:

Excel 2010
	D	E
2	126	4
Sheet3

Cell Formulas
Range	Formula
E2	=MEDIAN(0,D2-114,1)*(1+(FLOOR(D2,5)-110)/5)