Search Value by Range

[excelrooki]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Balance[/TD]
[TD]Fee ID[/TD]
[TD]Service Fee[/TD]
[TD]Fee ID[/TD]
[TD]From Bal[/TD]
[TD]To Bal[/TD]
[TD]Service Fee[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]$112.34[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]$0[/TD]
[TD]$499.99[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]$112.34[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]$500[/TD]
[TD]$1,499.99[/TD]
[TD]$20[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]$1234.21[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]$0[/TD]
[TD]$499.99[/TD]
[TD]$5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]$342.12[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]$500[/TD]
[TD]$1,299.99[/TD]
[TD]$15[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]$785.87[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]$1,300[/TD]
[TD]$1,499.99[/TD]
[TD]$25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need some help calculating the service fee based on the Balance and the Fee ID.

Example:
C2 should have a value of $10 since A2 is $112.34 and Fee Id is 1,
C3 should have a value of $5 since Balance is $112.34 and Fee ID is 2.
C4 should have a value of $20 since A4 is $1234.21 and Fee ID is 1

Thanks,
Jason

 

[Andrew Poulsom]

Formula in C2 copied down:

Excel 2010
	A	B	C	D	E	F	G
1	Balance	Fee ID	Service Fee	Fee ID	From Bal	To Bal	Service Fee
2	112.34	1	10	1	0	499.99	10
3	112.34	2	5	1	500	1,499.99	20
4	1234.21	1	20	2	0	499.99	5
5	342.12	2	5	2	500	1,299.99	15
6	785.87	2	15	2	1,300	1,499.99	25
Sheet1

Cell Formulas
Range	Formula
C2	=INDEX(G$2:G$6,MATCH(1,INDEX((D$2:D$6=B2)*(E$2:E$6<=A2)*(F$2:F6>=A2),),FALSE))

 

[Michael M]

This in C2 and drag down

=IF(AND(B2=1,A2<500),10,IF(AND(B2=2,A2<500),5,IF(AND(B2=1,A2<1500),20,IF(AND(B2=2,A2<1500),15,25))))

 

[excelrooki]

Thank you both, This solved my problem and answered my questions.