Using Vlookup then interpolate,return corresponding value, if not, return other value
 

Hi I am working in the following table for returning a corrsponding interpolated value.

Column A

88.6
84.1
98



Pay factor Look up Table.E1:F14

85 70
86 75.5
87 79.7
88 83.5
89 86.7
90 89.4
91 91.7
92 93.6
93 95.4
94 96.9
95 98.2
96 99.2
97 100
Under 85% 0


If the values in column A is any value from 97 to 85 = interpolate/return corrsponding value;
If the values in column A is any value greater than 97 = return 100
If the values in Column A is any value under 85= return 0



I used this formula in B1=
=IF(ISERROR(VLOOKUP(INT(A1),$E$1:$F$14,2,FALSE)*(I NT(A1)+1-A1)+VLOOKUP(INT(A1)+1,$E$1:$F$14,2,FALSE)*(A1-INT(A1))), "100",VLOOKUP(INT(A1),$E$2:$F$14,2,FALSE)*(INT(A1) +1-A1)+VLOOKUP(INT(A1)+1,$E$1:$F$14,2,FALSE)*(A1-INT(A1)))

Which returned the interpolated value of 88.6 from the table 85.4.
But it is returning any value which is not in the look up table as 100.

Can you please help me??

--------------------------------------------------------------------

Dear Wzaman, Good Evening.

Try to use this formula:
=IF(A1="","",IF(A1<$E$1,0,IF(A1$E$13,100;VLOOKUP( INT(A1),$E$1:$F$14,2,FALSE)*(INT(A1)+1-A1)+VLOOKUP(INT(A1)+1,$E$1:$F$14,2,FALSE)*(A1-INT(A1)))))

Tell me if it worked for you.