=IF(L74="Spiral Preformed steel galv straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"},{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,50 ,53.5,60}),IF(L74="Steel
PVC coated both sides straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"}),{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,5 0,53.5}))

I need a formula that will do this. This one confuses the computer when I
choose a number from the drop down list. Lets say 12x12x4 and spiral
preformed steel galv straight lengths my answer seems to be drawn to 50 for 9
out of 10 it will go to that number. How do I solve this I know it is
because I have ten "" text.

Thanks

try trim(G74) in your formula
it is acting like it g74 has a space in front of it
one way to check is to try = left(G74,1) and see what you get
also you only have 9 "xxx" in your formula and the 50 is the 9th place


"Excluxe" wrote:

=IF(L74="Spiral Preformed steel galv straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"},{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,50 ,53.5,60}),IF(L74="Steel
PVC coated both sides straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"}),{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,5 0,53.5}))

I need a formula that will do this. This one confuses the computer when I
choose a number from the drop down list. Lets say 12x12x4 and spiral
preformed steel galv straight lengths my answer seems to be drawn to 50 for 9
out of 10 it will go to that number. How do I solve this I know it is
because I have ten "" text.

Thanks

LOOKUP requires the search list to be in ascending order and yours isn't
(aren't). Sorted as text fields gives:

6x6x4
10x10x4
12x12x4
14x14x4
16x16x4
18x18x4
20x20x4
24x24x4
8x8x4


VLOOKUP would be a better option.

See VLOOKUP in HELP

"Excluxe" wrote:

=IF(L74="Spiral Preformed steel galv straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"},{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,50 ,53.5,60}),IF(L74="Steel
PVC coated both sides straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"}),{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,5 0,53.5}))

I need a formula that will do this. This one confuses the computer when I
choose a number from the drop down list. Lets say 12x12x4 and spiral
preformed steel galv straight lengths my answer seems to be drawn to 50 for 9
out of 10 it will go to that number. How do I solve this I know it is
because I have ten "" text.

Thanks

Sorted, it comes out like this:

10x10x4
12x12x4
14x14x4
16x16x4
18x18x4
20x20x4
24x24x4
6x6x4
8x8x4

That being said, there's all kinds of things going on with the posted
formula.

9 different sizes but 11 prices for the Spiral Preformed type.
Same sizes and the same first 10 prices for the PVC coated type.

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
LOOKUP requires the search list to be in ascending order and yours isn't
(aren't). Sorted as text fields gives:

6x6x4
10x10x4
12x12x4
14x14x4
16x16x4
18x18x4
20x20x4
24x24x4
8x8x4


VLOOKUP would be a better option.

See VLOOKUP in HELP

"Excluxe" wrote:

=IF(L74="Spiral Preformed steel galv straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"},{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,50 ,53.5,60}),IF(L74="Steel
PVC coated both sides straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"}),{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,5 0,53.5}))

I need a formula that will do this. This one confuses the computer when
I
choose a number from the drop down list. Lets say 12x12x4 and spiral
preformed steel galv straight lengths my answer seems to be drawn to 50
for 9
out of 10 it will go to that number. How do I solve this I know it is
because I have ten "" text.

Thanks

I didn't get out my microscope to the formulae ... just looked for the more
obvious!

P.S. Many congratulations on achieving MVP status: very well deserved.

"T. Valko" wrote:

Sorted, it comes out like this:

10x10x4
12x12x4
14x14x4
16x16x4
18x18x4
20x20x4
24x24x4
6x6x4
8x8x4

That being said, there's all kinds of things going on with the posted
formula.

9 different sizes but 11 prices for the Spiral Preformed type.
Same sizes and the same first 10 prices for the PVC coated type.

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
LOOKUP requires the search list to be in ascending order and yours isn't
(aren't). Sorted as text fields gives:

6x6x4
10x10x4
12x12x4
14x14x4
16x16x4
18x18x4
20x20x4
24x24x4
8x8x4


VLOOKUP would be a better option.

See VLOOKUP in HELP

"Excluxe" wrote:

=IF(L74="Spiral Preformed steel galv straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"},{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,50 ,53.5,60}),IF(L74="Steel
PVC coated both sides straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"}),{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,5 0,53.5}))

I need a formula that will do this. This one confuses the computer when
I
choose a number from the drop down list. Lets say 12x12x4 and spiral
preformed steel galv straight lengths my answer seems to be drawn to 50
for 9
out of 10 it will go to that number. How do I solve this I know it is
because I have ten "" text.

Thanks

Thank you!

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
I didn't get out my microscope to the formulae ... just looked for the more
obvious!

P.S. Many congratulations on achieving MVP status: very well deserved.

"T. Valko" wrote:

Sorted, it comes out like this:

10x10x4
12x12x4
14x14x4
16x16x4
18x18x4
20x20x4
24x24x4
6x6x4
8x8x4

That being said, there's all kinds of things going on with the posted
formula.

9 different sizes but 11 prices for the Spiral Preformed type.
Same sizes and the same first 10 prices for the PVC coated type.

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
LOOKUP requires the search list to be in ascending order and yours
isn't
(aren't). Sorted as text fields gives:

6x6x4
10x10x4
12x12x4
14x14x4
16x16x4
18x18x4
20x20x4
24x24x4
8x8x4


VLOOKUP would be a better option.

See VLOOKUP in HELP

"Excluxe" wrote:

=IF(L74="Spiral Preformed steel galv straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"},{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,50 ,53.5,60}),IF(L74="Steel
PVC coated both sides straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"}),{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,5 0,53.5}))

I need a formula that will do this. This one confuses the computer
when
I
choose a number from the drop down list. Lets say 12x12x4 and spiral
preformed steel galv straight lengths my answer seems to be drawn to
50
for 9
out of 10 it will go to that number. How do I solve this I know it is
because I have ten "" text.

Thanks

Set up a lookup table and use VLOOKUP function rather than hard code in the
formula


"Excluxe" wrote:

=IF(L74="Spiral Preformed steel galv straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"},{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,50 ,53.5,60}),IF(L74="Steel
PVC coated both sides straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"}),{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,5 0,53.5}))

I need a formula that will do this. This one confuses the computer when I
choose a number from the drop down list. Lets say 12x12x4 and spiral
preformed steel galv straight lengths my answer seems to be drawn to 50 for 9
out of 10 it will go to that number. How do I solve this I know it is
because I have ten "" text.

Thanks

I still confused but perhaps this will help explain what I am doing. I have
drop down list in L74 and G74 with the data in correct descending order. I
have used this formula multiple times but the other lists of sizes look like
this 2,3,4,5,6,etc this one is 6x6x4,8x8x4x10x10x4,etc. To bypass this
before I created and hid another drop down list that had the formula
=LOOKUP(A11,{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 ,16,17,18},{"0","8x6","8x8","10x6","10x8","10x10", "12x10","12x12","14x12","14x14","16x10","16x14","1 8x14","20x14","22x20","24x14","24x16","24x20","28x 18"})
where I would choose a number instead of a size this though doesn't work
either for this formula and it forces me to create so many sub formulas that
I have 10 plus formulas hidden throughout my spreadsheet which is confusing.



"Teethless mama" wrote:

Set up a lookup table and use VLOOKUP function rather than hard code in the
formula


"Excluxe" wrote:

=IF(L74="Spiral Preformed steel galv straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"},{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,50 ,53.5,60}),IF(L74="Steel
PVC coated both sides straight
lengths",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12 x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24 x24x4"}),{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,5 0,53.5}))

I need a formula that will do this. This one confuses the computer when I
choose a number from the drop down list. Lets say 12x12x4 and spiral
preformed steel galv straight lengths my answer seems to be drawn to 50 for 9
out of 10 it will go to that number. How do I solve this I know it is
because I have ten "" text.

Thanks

So has the problem been solved?

FWIW, I have the formula as structurally flawed in addition to what has
already been pointed out. The structure of the second (nested) IF function is
as follows (with text and array contents abbreviated):
IF(L74="Steel...",LOOKUP(G74,{"6x6x4","8x8x4","10x 10x4"}),{7.35,9.05,12.7})
Note how the last array containing numbers is actually the third argument of
the IF function and not the third argument of the LOOKUP function.

The following achieves what I assume is your intention. The array of numbers
is converted to the third argument of the LOOKUP function and the third
argument of the IF function is "No result" (change to suit).

=IF(L74="Spiral...",LOOKUP(G74,{"6x6x4","8x8x4","1 0x10x4","12x12x4","14x14x4","16x16x4","18x18x4","2 0x20x4","24x24x4"},{7.35,9.05,12.7,14.35,20.5,32.5 ,41,44.5,50,53.5,60}),IF(L74="Steel...",LOOKUP(G74 ,{"6x6x4","8x8x4","10x10x4","12x12x4","14x14x4","1 6x16x4","18x18x4","20x20x4","24x24x4"},{7.35,9.05, 12.7,14.35,20.5,32.5,41,44.5,50,53.5}),"No result"))

However, the above is just academic since I agree with teethless mama and
think it should be rewritten altogether. Also, as pointed out by T. Valco,
you have more elements in the numeric arrays than in the size arrays.

Greg

That formula still won't work properly. As Toppers pointed out, the
lookup_vector *must* be sorted ascending. Place all the size elements in a
range of cells and then do an ascending sort on them and see how Excel sorts
them.

--
Biff
Microsoft Excel MVP


"Greg Wilson" wrote in message
...
So has the problem been solved?

FWIW, I have the formula as structurally flawed in addition to what has
already been pointed out. The structure of the second (nested) IF function
is
as follows (with text and array contents abbreviated):
IF(L74="Steel...",LOOKUP(G74,{"6x6x4","8x8x4","10x 10x4"}),{7.35,9.05,12.7})
Note how the last array containing numbers is actually the third argument
of
the IF function and not the third argument of the LOOKUP function.

The following achieves what I assume is your intention. The array of
numbers
is converted to the third argument of the LOOKUP function and the third
argument of the IF function is "No result" (change to suit).

=IF(L74="Spiral...",LOOKUP(G74,{"6x6x4","8x8x4","1 0x10x4","12x12x4","14x14x4","16x16x4","18x18x4","2 0x20x4","24x24x4"},{7.35,9.05,12.7,14.35,20.5,32.5 ,41,44.5,50,53.5,60}),IF(L74="Steel...",LOOKUP(G74 ,{"6x6x4","8x8x4","10x10x4","12x12x4","14x14x4","1 6x16x4","18x18x4","20x20x4","24x24x4"},{7.35,9.05, 12.7,14.35,20.5,32.5,41,44.5,50,53.5}),"No
result"))

However, the above is just academic since I agree with teethless mama and
think it should be rewritten altogether. Also, as pointed out by T. Valco,
you have more elements in the numeric arrays than in the size arrays.

Greg

I know. The comment was characterized as being "in addition to what has
already been pointed out" and was "just academic since I agree with teethless
mama". There's also value in knowing that the formula is structurally flawed.

I never mentioned it, but I was thinking Match and Index since the ascending
sort order isn't obvious and may get changed in the future - and screwed up
in the process.

Regards,
Greg

"T. Valko" wrote:

That formula still won't work properly. As Toppers pointed out, the
lookup_vector *must* be sorted ascending. Place all the size elements in a
range of cells and then do an ascending sort on them and see how Excel sorts
them.

--
Biff
Microsoft Excel MVP


"Greg Wilson" wrote in message
...
So has the problem been solved?

FWIW, I have the formula as structurally flawed in addition to what has
already been pointed out. The structure of the second (nested) IF function
is
as follows (with text and array contents abbreviated):
IF(L74="Steel...",LOOKUP(G74,{"6x6x4","8x8x4","10x 10x4"}),{7.35,9.05,12.7})
Note how the last array containing numbers is actually the third argument
of
the IF function and not the third argument of the LOOKUP function.

The following achieves what I assume is your intention. The array of
numbers
is converted to the third argument of the LOOKUP function and the third
argument of the IF function is "No result" (change to suit).

=IF(L74="Spiral...",LOOKUP(G74,{"6x6x4","8x8x4","1 0x10x4","12x12x4","14x14x4","16x16x4","18x18x4","2 0x20x4","24x24x4"},{7.35,9.05,12.7,14.35,20.5,32.5 ,41,44.5,50,53.5,60}),IF(L74="Steel...",LOOKUP(G74 ,{"6x6x4","8x8x4","10x10x4","12x12x4","14x14x4","1 6x16x4","18x18x4","20x20x4","24x24x4"},{7.35,9.05, 12.7,14.35,20.5,32.5,41,44.5,50,53.5}),"No
result"))

However, the above is just academic since I agree with teethless mama and
think it should be rewritten altogether. Also, as pointed out by T. Valco,
you have more elements in the numeric arrays than in the size arrays.

Greg