Search Value by Range

[excelrooki]

	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		1	$0	$499.99	$10
3	$112.34	2		1	$500	$1,499.99	$20
4	$1234.21	1		2	$0	$499.99	$5
5	$342.12	2		2	$500	$1,299.99	$15
6	$785.87	2		2	$1,300	$1,499.99	$25

<tbody>
</tbody>

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.