Alternate to nested IFs, comparing values in between percentages

[Mplz]

Hello all!

There has to be an easier way to accomplish what I'm after, would love any ideas. I am trying to write a formula that spits out a value based on what range the percentage falls in. I'm currently using nested IFs to determine if the percentage falls in between the ranges below and gives me the value in the third column.

3.0%​	3.4%​	1500​
3.5%​	3.9%​	1410​
4.0%​	4.4%​	1320​
4.5%​	4.9%​	1230​
5.0%​	5.4%​	1140​
5.5%​	5.9%​	1050​

=IF($P$10<$AL$17,$AN$17,IF(AND($P$10>$AL$18,$P$10<$AM$18),AN18,IF(AND($P$10>$AL$19,$P$10<$AM$19),$AN$19,IF(AND($P$10>$AL$20,$P$10<$AM$20),$AN$20,IF(AND($P$10>$AL$21,$P$10<$AM$21),$AN$21,IF(AND($P$10>$AL$22,$P$10<$AM$22),$AN$22,IF(AND($P$10>$AL$23,$P$10<$AM$23),$AN$23,IF($P$10=$AL$24,AN24,IF($P$10>$AL$24,$AN$25,"0")))))))))

This code is basically if my value is greater than 3, and less than 3.4, give me 1500, if false repeat for 3.5-3.9 etc. I've tried to mess around with Index Match but haven't found any solution yet. Thanks in advance!

 

[Fluff]

How about

Fluff.xlsm
	A	B	C							J	K	L
1
2	3.1%	1500								3.00%	3.40%	1500
3	3.9%	1410								3.50%	3.90%	1410
4	5.2%	1140								4.00%	4.40%	1320
5										4.50%	4.90%	1230
6										5.00%	5.40%	1140
7										5.50%	5.90%	1050
Sheet6

Cell Formulas
Range	Formula
B2:B4	B2	=INDEX($L$2:$L$7,MATCH(A2,$J$2:$J$7,1))

 

[Mplz]

Fluff this is perfect!

I was overthinking it trying to find a way to compare the two percentage values, but since they are continuous it really only needs to look at one column. Thank you so much.

Adding here in case someone finds it. I have edge cases that capture everything outside of this range, and in this case I had to add a 0% to get everything below 3% and 6.1% to get everything above 6%.

Garage KPI.xlsx
	AI	AJ	AK	AL	AM	AN
17	Percent	2%		0.0%		1500
18	Amount	1500		3.0%	3.4%	1500
19				3.5%	3.9%	1410
20				4.0%	4.4%	1320
21				4.5%	4.9%	1230
22				5.0%	5.4%	1140
23				5.5%	5.9%	1050
24				6.0%		960
25				6.1%		0
KPI Tracker

Cell Formulas
Range	Formula
AJ18	AJ18	=INDEX($AN$17:$AN$25,MATCH(AJ17,$AL$17:$AL$25,1))

 

[Fluff]

You're welcome & thanks for the feedback.