lookup formulas dependent upon lookup formulas

[Skibee]

Hi

I have the following formula:
=LOOKUP(L7,{0,15,15.1,15.4,15.5,15.9,16,16.9,17,17.9,18,18.9,19,19.9,20,20.9,21,21.9,22,22.9,23,23.9,24,24.9,25.0259},{"1.00","1.00","2.50","2.50","4.00","4.00","5.50","5.50","6.50","6.50","7.00","7.00","8.75","8.75","9.50","9.50","10.75","10.75","12.00","12.00","13.25","13.25","14.50""14.50","15.75","15.75"})

So the result of this lookup operation may be:

L7=2.50
L8=9.50
L9=13.25
and so on down to L650

Now I need to take this initial look up result and say if:
L7=1.00 return 0.00
if 2.50 return 1.75
if 4.00 return 3.50
if 5.50 return £4.50
and so on with 13 possible answers.

I have tried to do this using a further look-up formula, but it just returns
#NA! even when the L7 or L8 etc. is populated. Does anyone know how I get
round this?
Many thanks for any help.
Regards Liz

 

[driller]

Without knowing where you are headed,

for a starter, i just insert the new condition u like but without removing
any possible logical conflict from your posted formula...I just insert the
new lookup_vector and Result_vector in the braced fields...

Now I need to take this initial look up result and say if:
L7=1.00 return 0.00
if 2.50 return 1.75
if 4.00 return 3.50
if 5.50 return £4.50

test this one..
=lookup(L7,{1,0,1.75,3.5,4.5,1,2.5,2.5,4,4,5.5,5.5,6.5,6.5,7,7,8.75,8.75,9.5,9.5,10.75,10.75,12,12,13.25,13.25,14.5,14.5,15.75,15.75},{0,1,2.5,4,5.5,15,15.1,15.4,15.5,15.9,16,16.9,17,17.9,18,18.9,19,19.9,20,20.9,21,21.9,22,22.9,23,23.9,24,24.9,25,25.9})

i intentionally remove all the "quotes" to read easily for testing...can
insert them back afterwards...

--
regards,
driller

*****
- dive with Jonathan Seagull