Using the lookup result as a value

[CharlCT]

Hi

I am having a problem writing a macro that sources one of its values from a cell that contains the following formula =LOOKUP(J9,{0,80,85,90,95},{"0%","40%","60%","80%","100%"}).

The above formula correctly returns '80%' but when I use this cell as an input for another formula Excel reads it as '0%'.

Can, and if so how does, one use the resulting value of the lookup function as a fixed value for another function?

Any help would be much appreciated

Regards
Charl

[Cheeky Charlie]

your lookup formula returns strings

i.e. it returns the 'word' 80%, which Excel thinks of as a word with three letters: 8, 0, %. When you try to do maths with words Excel gives your word a value of 0.

Try typing these into two cells:
=n("80%")
=n(80%)

(n coerces its argument to a number)

So, in summary, replace "0%" with 0% etc. in your lookup

HTH

[Special-K]

A quick test reveals "60%" is interpreted as 0.6, I suspect therefore that your problem lies in the "other formula".

[CharlCT]

Hi

Got your reply, CheekyCharlie and rewrote the formula as =LOOKUP(J9,{0,80,85,90,95},{0,25,50,75,100}) which returned 80 and then auto-summed that value to another cell to give me a 'cleaner' value which returned 8000%. I divided by 100 and it now gives me the correct input of 80%. I am using lookup on a lookup result so that may be why the values are quirky (plus the fact that I am new to the lookup function which seems far more likely).

Thanks to you and Special-K for your speedy assistance.

Regards
Charl

[Cheeky Charlie]

80% = 0.8

Divide by 100 within the encapsulated vlookup array.

[Cheeky Charlie]

Special K.

I did this test, I also did the tests I outlined. Have you tried coercing the string "80%" and the percentage 80% as I describe in my first post?

If the results are not what I outline, what version of Excel are you using? This seems very strange... what I describe works perfectly on my machine!