Excel Formula to return rate in cell based on entry of a percentage.

[Lmfacc]

Hi there!
I am a bit of a novice so I apologize in advance for the terminology I use. I am hoping for some assistance in a calculator I have created and am currently enhancing for ease of use for the user.
Based on user entry of a percentage into one cell, I would like to populate a rate in another.
The percentages have various ranges.
For example:
If Cell A1 = 13% then I would like cell B1 to populate to 0.0187
The ranges are <=13.6%, between 13.7-14.8, between 14.9-15.8, between 15.9-16.8 and between 16.9-17., with each range populating a rate.
I have accomplished how to compare for up to 3 of ranges, but can’t figure out how to make it run for anything more.
Currently my formula for another scenario:
IF(A1<=7.1,0.0282,IF(A1<1.2,0.021,0.319))
Similar in premise if A1 is less than or equal to 7.1 return 0.0282, If A1 is less than 1.2 than return 0.021, anything in between return 0.319 This has functionality and works for my calculator.
But I don’t know how to apply something like this for a fuller range as specified above. Any insight would be so helpful.
thank you ever so kindly

 

[Dave Patton]

Edit the following for your rates.
N.B. C2 is self-contained; the array includes the information from the table of rates.

T202010b.xlsm
	A	B	C	D	E	F
1
2	0.17	0.0500	0.05		0.0%	0.0187
3					13.6%	0.02
4					14.8%	0.03
5					15.8%	0.04
6					16.8%	0.05
7					17.1%	0.06
8
1f

Cell Formulas
Range	Formula
B2	B2	=(A2>0)*LOOKUP(A2,E2:F7)
C2	C2	=(A2>0)*LOOKUP(A2,{0,0.0187;0.1361,0.02;0.1481,0.03;0.1581,0.04;0.1681,0.05;0.171,0.06})

 

[Lmfacc]

Edit the following for your rates.
N.B. C2 is self-contained; the array includes the information from the table of rates.

T202010b.xlsm
	A	B	C	D	E	F
1
2	0.17	0.0500	0.05		0.0%	0.0187
3					13.6%	0.02
4					14.8%	0.03
5					15.8%	0.04
6					16.8%	0.05
7					17.1%	0.06
8
1f

Cell Formulas
Range	Formula
B2	B2	=(A2>0)*LOOKUP(A2,E2:F7)
C2	C2	=(A2>0)*LOOKUP(A2,{0,0.0187;0.1361,0.02;0.1481,0.03;0.1581,0.04;0.1681,0.05;0.171,0.06})

Thank you Dave! This seems very reasonable! I am going to give it a try now! So very much appreciated

 

[Dave Patton]

Check the ranges or results at the upper and lower limits of the ranges.
Check that amounts like 13.6 yield the correct result.

The ranges are not as they appear; I added .001. See the the details or cell contents in the initial post or the following.

T202010b.xlsm
	A	B	C	D	E	F
2	0.148	0.0200	0.02		0.00%	0.0187
3					13.61%	0.02
4					14.81%	0.03
5					15.81%	0.04
6					16.81%	0.05
7					17.10%	0.06
8
1f

Cell Formulas
Range	Formula
B2	B2	=(A2>0)*LOOKUP(A2,E2:F7)
C2	C2	=(A2>0)*LOOKUP(A2,{0,0.0187;0.1361,0.02;0.1481,0.03;0.1581,0.04;0.1681,0.05;0.171,0.06})