d0wnt0wn,
Typically, a Lookup formula like =LOOKUP(E16,{0,1,2,3,4,5,7,9,11,12,14,16},{1.02,1.03,1.04,1.06,1.08,1.12,1.2,1.3,1.41,1.54,1.67,1.8}) can be used to return values from the second array based on trying to match E16 in the first array. The nature of Lookup is such that if for example the lookup value E16 is say 16.5 it will return a match based on the largest number smaller than 16.5. In this case that would be 16 and would therefore return 1.8 from the second array. A value of 15 would return 1.67 based on a nearest match with 14. This is essentially a way of returning values based on E16 being Greater Than or Equal To thelookup values in the first array.
Note that if E16 is equal to 16 it will match 16 and return 1.8 - which is not what you are wanting.
=LOOKUP(E16,{0,1,2,3,4,5,7,9,11,12,14,16}+10^-10,{1.02,1.03,1.04,1.06,1.08,1.12,1.2,1.3,1.41,1.54,1.67,1.8})
Because you wanted only Greater Than, I added the +10^-10 element as a modifier to the first array. 10 raised to the power negative 10 creates avery small fraction which hopefully is negligible compared to your actual values of E16 (?) Adding that small fraction to the values in first array means that typically, the value 16 becomes 16.0000000001
Now if E16 is equal to 16 it will not match with 16.0000000001 as it is larger in value than E16. It now matches with the nearest, smaller, non exact match, which is 14.0000000001 and therefore returns 1.67 as you would want.
If as I now understand, you want a multiplier of 1 if E16 = 0 and the other lookup multipliers for E16 values exceeding 0 then maybe this is the formula you need......
=IF(E16=0,D16,LOOKUP(E16,{0,1,2,3,4,5,7,9,11,12,14,16}+10^-10,{1.02,1.03,1.04,1.06,1.08,1.12,1.2,1.3,1.41,1.54,1.67,1.8})*D16)
I hope that makes sense and is of help?
