NEED AN EQUATION FOR FINDING THE VALUE

[Shinod]

Dear Excel Experts, I'm looking for an equally:

Need to get the corresposing percent in F2 when i enter a value in E2 cell.

Thanks in advance

Book1
	A	B	C	D	E	F
1	0	150,000	10.00%
2	150,001	200,000	9.50%		15,000
3	200,001	250,000	9.00%
4	250,001	300,000	8.50%
5	300,001	350,000	8.00%
6	350,001	400,000	7.50%
7	400,001	450,000	7.00%
8	450,001	500,000	6.50%
9	500,001	550,000	6.00%
10	550,001	600,000	5.50%
11	600,001	650,000	5.00%
12	650,001	700,000	4.50%
13	700,001	750,000	4.00%
14	750,001	800,000	3.50%
15	800,001	850,000	3.00%
16	850,001	900,000	2.50%
17	900,001	950,000	2.00%
18	950,001	1,000,000	1.50%
19	1,000,001	Above	1.00%
20
21
Sheet1

Cell Formulas
Range	Formula
B2:B18	B2	=+B1+50000
A2:A19	A2	=+B1+1

 

[etaf]

how about
=LOOKUP(2,1/((E2>=$A$1:$A$19)*(E2<=$B$1:$B$19)),$C$1:$C$19)

Book21
	A	B	C	D	E	F	G	H
1	0	150000	0.1
2	150001	200000	0.095		15000	#N/A		0.1
3	200001	250000	0.09		11100000000			0.01
4	250001	300000	0.085		12			0.1
5	300001	350000	0.08		180000			0.095
6	350001	400000	0.075
7	400001	450000	0.07
8	450001	500000	0.065
9	500001	550000	0.06
10	550001	600000	0.055
11	600001	650000	0.05
12	650001	700000	0.045
13	700001	750000	0.04
14	750001	800000	0.035
15	800001	850000	0.03
16	850001	900000	0.025
17	900001	950000	0.02
18	950001	1000000	0.015
19	1000001	Above	0.01
Sheet2

Cell Formulas
Range	Formula
F2	F2	=INDEX(C1:C18,MATCH(E2,B1:B18,1))
H2:H5	H2	=LOOKUP(2,1/((E2>=$A$1:$A$19)*(E2<=$B$1:$B$19)),$C$1:$C$19)
B2:B18	B2	=+B1+50000
A2:A19	A2	=+B1+1

 

[Peter_SSs]

.. or just change the 'match' column from B to A? Or the equivalent VLOOKUP.

23 04 17.xlsm
	A	B	C	D	E	F	G
1	0	150,000	10.00%
2	150,001	200,000	9.50%		15,000	10.00%	10.00%
3	200,001	250,000	9.00%		11,100,000,000	1.00%	1.00%
4	250,001	300,000	8.50%		12	10.00%	10.00%
5	300,001	350,000	8.00%		180,000	9.50%	9.50%
6	350,001	400,000	7.50%
7	400,001	450,000	7.00%
8	450,001	500,000	6.50%
9	500,001	550,000	6.00%
10	550,001	600,000	5.50%
11	600,001	650,000	5.00%
12	650,001	700,000	4.50%
13	700,001	750,000	4.00%
14	750,001	800,000	3.50%
15	800,001	850,000	3.00%
16	850,001	900,000	2.50%
17	900,001	950,000	2.00%
18	950,001	1,000,000	1.50%
19	1,000,001	Above	1.00%
20
Lookup %

Cell Formulas
Range	Formula
F2:F5	F2	=INDEX(C$1:C$19,MATCH(E2,A$1:A$19))
G2:G5	G2	=VLOOKUP(E2,A$1:C$19,3)

 

[Shinod]

Not working properly. I',m getting the same #N/A

 

[Shinod]

I'm getting 10% for 180,000. It is supposed to be 9.5%

 

[Peter_SSs]

I'm getting 10% for 180,000. It is supposed to be 9.5%

Since you have had two people respond and several formulas suggested, when reporting results, please make it clear who you are speaking to and which formula.

As you can see, both my two formulas and @etaf's formula in H5 of their sheet are returning 9.5% for 180,000.

Perhaps you could post your mini sheet again with the relevant formula(s) inserted and showing the wrong result?