What formula to use and how?

[Guest]

I have a cost sheet i putting together. I have five sets of information.
Short example is Size 1,2,3,4,5 Price 2,4,6,8,10. I need a formula which
will allow me to draw information from these sets. I have place the size in
a drop down list and using a lookup formula can find the prices by select
whatever size I need but now I need to be able to due this based of another
parameter.
=IF(A2=1,"=LOOKUP(A3,{1,4,5,6,7,8,10,12,14,16,18,20,22,24,30,36},{1.08,1.23,1.45,1.62,2,2.46,2.85,4.24,4.7,5.25,5.8,6.3,9.55,11.45})",IF(A2=2,"B",
IF(A2=3,"C",IF(A2=4,"D","F")))) This formula is what I need but doesn't
work it just lists the secondary formula as text.


Here is how my table looks like

Insulation
Price based type of insulation
Size 1 2 3 4 5
2 .2 .4 .8 1.6 3.2
4 .4 .6 1.2 2.4 4.8
6 .6 .8 1.6 3.2 6.4
8 .8 1 2 4 8

Sizes are the column going down and Types of insulation and there respective
price go across. I am place this info into a sheet that would allow me to
pick a size from a pull down list then an insulation type from a pull down
list and then have the prices called up automatically. Any help would be
appreciated thanks

 

[Peo Sjoblom]

I gave you an answer yesterday, it's better that you continue that thread
instead of posting the same question again and if that answer didn't work
post back explain what didn't work

 

[Guest]

I rewrote this because my other post has no response. If you posted on it
then either I can't see for whatever strange reason or something else
happened. If you could repost your answer it would be appreciated.

 

[Peo Sjoblom]

OK, here it is

=IF(A2=1,LOOKUP(A3,{1,4,5,6,7,8,10,12,14,16,18,20,22,24,30,36},{1.08,1.23,1.45,1.62,2,2.46,2.85,4.24,4.7,5.25,5.8,6.3,9.55,11.45}),IF(A2=2,"B",
IF(A2=3,"C",IF(A2=4,"D","F"))))

I assume you will replace B, C etc with other versions of the formula, make
sure you don't have quotations like "lookup(etc)"

Another way would be to use


=CHOOSE(A2,LOOKUP(A3,{1,4,5,6,7,8,10,12,14,16,18,20,22,24,30,36},{1.08,1.23,1.45,1.62,2,2.46,2.85,4.24,4.7,5.25,5.8,6.3,9.55,11.45}),"B","C","D")

and of course replace the same way if necessary, it's a bit shorter



Btw, if you have access to a newsreader it is much easier to find your own
posts etc and you can flag posts for follow ups etc. The web interface kinda
stinks but if no other alternative I can se it is necessary

 

[Guest]

Thanks it works great

OK, here it is

=IF(A2=1,LOOKUP(A3,{1,4,5,6,7,8,10,12,14,16,18,20,22,24,30,36},{1.08,1.23,1.45,1.62,2,2.46,2.85,4.24,4.7,5.25,5.8,6.3,9.55,11.45}),IF(A2=2,"B",
IF(A2=3,"C",IF(A2=4,"D","F"))))

I assume you will replace B, C etc with other versions of the formula, make
sure you don't have quotations like "lookup(etc)"

Another way would be to use


=CHOOSE(A2,LOOKUP(A3,{1,4,5,6,7,8,10,12,14,16,18,20,22,24,30,36},{1.08,1.23,1.45,1.62,2,2.46,2.85,4.24,4.7,5.25,5.8,6.3,9.55,11.45}),"B","C","D")

and of course replace the same way if necessary, it's a bit shorter



Btw, if you have access to a newsreader it is much easier to find your own
posts etc and you can flag posts for follow ups etc. The web interface kinda
stinks but if no other alternative I can se it is necessary

 

[David Biddulph]

Peo's reply is at or at
http://groups.google.co.uk/group/mi...6a61e53b476?lnk=st&q=&rnum=1#c7a626a61e53b476
(http://preview.tinyurl.com/ytkk5f)