Issue returning a value based on values between two percentage points

[joshmarsh]

Hi I am trying to create a formula that returns the value based on a percentage of the data set below. Where i struggling is returning a value that is not listed below, but is between two numbers. I cant use a straight line allocation because the % increased of values on the right aren't equal compared to the left.

For example, i want to return a value from the right hand column for a 15.55% CAGR... but i need to be able to do this for 0-100% CAGR

TSR CAGR	% of Target PVUs that became Earned PVUs*
20%	200%
19.00%	190%
18.00%	175%
17.00%	160%
16.00%	145%
15.00%	130%
14.00%	115%
13.00%	100%
12.00%	90%
11.00%	80%
10.00%	70%
9.00%	60%
8.00%	50%
7.00%	40%
6.00%	30%
5.00%	25%

 

[awoohaw]

OKay, After I investigated the scenario more, I think my guess is correct. So, by wrapping the formula with an IF statement when there is an exact match with the Target in the Lookup column and then doing a simple lookup you'll get what you want (I think it is what you want). See the mini worksheet below (by the way, please use the xl2bb add in to post your worksheets details. Images do not help much.

Mr Excel Questions 71.xlsm
	A	B	C	D	E
1	TSR CAGR	% of Target PVUs that became Earned PVUs*		Target	Result
2	20.00%	200.00%		15.55%	138.25%
3	19.00%	190.00%		15.33%	134.88%
4	18.00%	175.00%		16.50%	152.57%
5	17.00%	160.00%		5.82%	29.10%
6	16.00%	145.00%		10.00%	70.00%
7	15.00%	130.00%		5.83%	29.17%
8	14.00%	115.00%		10.00%	70.00%
9	13.00%	100.00%		14.60%	124.00%
10	12.00%	90.00%		13.47%	106.98%
11	11.00%	80.00%		19.67%	196.65%
12	10.00%	70.00%		18.27%	179.01%
13	9.00%	60.00%
14	8.00%	50.00%
15	7.00%	40.00%
16	6.00%	30.00%
17	5.00%	25.00%
denzo36

Cell Formulas
Range	Formula
E2:E12	E2	=LET(target, $D2, Tgtlu1, XLOOKUP($D2,$A$2:$A$17,$A$2:$A$17,,-1,-1), Tgtlu2,XLOOKUP($D2,$A$2:$A$17,$A$2:$A$17,,1,-1), interppct, (target - Tgtlu1)/(Tgtlu2-Tgtlu1), Lkup1,XLOOKUP($D2,$A$2:$A$17,$B$2:$B$17,,-1,-1), lkup2, XLOOKUP($D2,$A$2:$A$17,$B$2:$B$17,,1,-1), DiffLu1Lu2, lkup2-Lkup1, InterpAdd, interppct * DiffLu1Lu2, InterpLookUp, Lkup1 + InterpAdd, IF(ISNUMBER(MATCH(target,$A$2:$A$17,0))=FALSE,InterpLookUp,XLOOKUP(target,$A$2:$A$17,$B$2:$B$17,,0)))