Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
=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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
Create a table as below: as the data appears to be the same for Spiral..."
and "Steel ..." you only need columns A & B : C is redundant. As noted by other respondents, the table elements don't match: I've just copied as you had them. On Sheet1 (change in formula if not this sheet) A B C 6x6x4 7.35 7.35 8x8x4 9.05 9.05 10x10x4 12.70 12.70 12x12x4 14.35 14.35 14x14x4 20.50 20.50 16x16x4 32.50 32.50 18x18x4 41.00 41.00 20x20x4 44.50 44.50 24x24x4 50.00 50.00 53.50 53.50 60.00 Then ... =IF(OR(L74="Spiral Preformed steel galv straight lengths",L74="Steel PVC coated both sides straight lengths"),IF(ISNA(VLOOKUP(G47,Sheet1!A:B,2,0)),"No match",VLOOKUP(G47,Sheet1!A:B,2,0)),"") IF L74 can only be the above values, the test for L74 is also redundant. HTH "Greg Wilson" wrote: 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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
So this is getting to what need I just can't get to work though. I just get
no match. My data is on sheet 3 in columns a and b. I have this into the formula and it still doesn't work. Also I would like to apoligize I grabbed, by mistake, an older formula that wasn't completely down which is why it appeared that numbers were missing. When complete though the formula that I posted still didn't work. Here is my data A B C 6x6x4 7.35 14.55 8x8x4 9.05 18.40 10x10x4 12.70 24 12x12x4 14.35 28 14x14x4 20.50 46.50 16x16x4 32.50 58 18x18x4 41 91 20x20x4 44.50 116 22x22x4 50 132 24x24x4 53 144 Then using a pull down list in L74 it should be able to choose which pricing column to use either b or c then match it to the size that i pick from a pull down menu in column G74 Which what your formula does I believe but I can't get to work. Once again sorry about the formula screw up. "Toppers"wrote: Create a table as below: as the data appears to be the same for Spiral..." and "Steel ..." you only need columns A & B : C is redundant. As noted by other respondents, the table elements don't match: I've just copied as you had them. On Sheet1 (change in formula if not this sheet) A B C 6x6x4 7.35 7.35 8x8x4 9.05 9.05 10x10x4 12.70 12.70 12x12x4 14.35 14.35 14x14x4 20.50 20.50 16x16x4 32.50 32.50 18x18x4 41.00 41.00 20x20x4 44.50 44.50 24x24x4 50.00 50.00 53.50 53.50 60.00 Then ... =IF(OR(L74="Spiral Preformed steel galv straight lengths",L74="Steel PVC coated both sides straight lengths"),IF(ISNA(VLOOKUP(G47,Sheet1!A:B,2,0)),"No match",VLOOKUP(G47,Sheet1!A:B,2,0)),"") IF L74 can only be the above values, the test for L74 is also redundant. HTH "Greg Wilson" wrote: 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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
To add on to what I just wrote is it possible to use a hlookup to choose to
column with the correct pricelist and then vlookup to match the size to the price? "Toppers" wrote: Create a table as below: as the data appears to be the same for Spiral..." and "Steel ..." you only need columns A & B : C is redundant. As noted by other respondents, the table elements don't match: I've just copied as you had them. On Sheet1 (change in formula if not this sheet) A B C 6x6x4 7.35 7.35 8x8x4 9.05 9.05 10x10x4 12.70 12.70 12x12x4 14.35 14.35 14x14x4 20.50 20.50 16x16x4 32.50 32.50 18x18x4 41.00 41.00 20x20x4 44.50 44.50 24x24x4 50.00 50.00 53.50 53.50 60.00 Then ... =IF(OR(L74="Spiral Preformed steel galv straight lengths",L74="Steel PVC coated both sides straight lengths"),IF(ISNA(VLOOKUP(G47,Sheet1!A:B,2,0)),"No match",VLOOKUP(G47,Sheet1!A:B,2,0)),"") IF L74 can only be the above values, the test for L74 is also redundant. HTH "Greg Wilson" wrote: 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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
Send me w/book:
toppers <at NOSPAMjohntopley.fsnet.o.uk Remove NOSPAM "Excluxe" wrote: To add on to what I just wrote is it possible to use a hlookup to choose to column with the correct pricelist and then vlookup to match the size to the price? "Toppers" wrote: Create a table as below: as the data appears to be the same for Spiral..." and "Steel ..." you only need columns A & B : C is redundant. As noted by other respondents, the table elements don't match: I've just copied as you had them. On Sheet1 (change in formula if not this sheet) A B C 6x6x4 7.35 7.35 8x8x4 9.05 9.05 10x10x4 12.70 12.70 12x12x4 14.35 14.35 14x14x4 20.50 20.50 16x16x4 32.50 32.50 18x18x4 41.00 41.00 20x20x4 44.50 44.50 24x24x4 50.00 50.00 53.50 53.50 60.00 Then ... =IF(OR(L74="Spiral Preformed steel galv straight lengths",L74="Steel PVC coated both sides straight lengths"),IF(ISNA(VLOOKUP(G47,Sheet1!A:B,2,0)),"No match",VLOOKUP(G47,Sheet1!A:B,2,0)),"") IF L74 can only be the above values, the test for L74 is also redundant. HTH "Greg Wilson" wrote: 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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
Here's a small sample file:
http://cjoint.com/?hmtvfq5I7N Notice how the the names in the Type drop down list *match* the column headers of the table. -- Biff Microsoft Excel MVP "Excluxe" wrote in message ... To add on to what I just wrote is it possible to use a hlookup to choose to column with the correct pricelist and then vlookup to match the size to the price? "Toppers" wrote: Create a table as below: as the data appears to be the same for Spiral..." and "Steel ..." you only need columns A & B : C is redundant. As noted by other respondents, the table elements don't match: I've just copied as you had them. On Sheet1 (change in formula if not this sheet) A B C 6x6x4 7.35 7.35 8x8x4 9.05 9.05 10x10x4 12.70 12.70 12x12x4 14.35 14.35 14x14x4 20.50 20.50 16x16x4 32.50 32.50 18x18x4 41.00 41.00 20x20x4 44.50 44.50 24x24x4 50.00 50.00 53.50 53.50 60.00 Then ... =IF(OR(L74="Spiral Preformed steel galv straight lengths",L74="Steel PVC coated both sides straight lengths"),IF(ISNA(VLOOKUP(G47,Sheet1!A:B,2,0)),"No match",VLOOKUP(G47,Sheet1!A:B,2,0)),"") IF L74 can only be the above values, the test for L74 is also redundant. HTH "Greg Wilson" wrote: 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 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
Thank you all for your patience with me.
I have discovered a formula which works for me so far =VLOOKUP(C271,AU19:AX93,MATCH(G271,AU18:AX18,0),0) so this problem has been diverted. Thanks "T. Valko" wrote: Here's a small sample file: http://cjoint.com/?hmtvfq5I7N Notice how the the names in the Type drop down list *match* the column headers of the table. -- Biff Microsoft Excel MVP "Excluxe" wrote in message ... To add on to what I just wrote is it possible to use a hlookup to choose to column with the correct pricelist and then vlookup to match the size to the price? "Toppers" wrote: Create a table as below: as the data appears to be the same for Spiral..." and "Steel ..." you only need columns A & B : C is redundant. As noted by other respondents, the table elements don't match: I've just copied as you had them. On Sheet1 (change in formula if not this sheet) A B C 6x6x4 7.35 7.35 8x8x4 9.05 9.05 10x10x4 12.70 12.70 12x12x4 14.35 14.35 14x14x4 20.50 20.50 16x16x4 32.50 32.50 18x18x4 41.00 41.00 20x20x4 44.50 44.50 24x24x4 50.00 50.00 53.50 53.50 60.00 Then ... =IF(OR(L74="Spiral Preformed steel galv straight lengths",L74="Steel PVC coated both sides straight lengths"),IF(ISNA(VLOOKUP(G47,Sheet1!A:B,2,0)),"No match",VLOOKUP(G47,Sheet1!A:B,2,0)),"") IF L74 can only be the above values, the test for L74 is also redundant. HTH "Greg Wilson" wrote: 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 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Excluxe" wrote in message ... Thank you all for your patience with me. I have discovered a formula which works for me so far =VLOOKUP(C271,AU19:AX93,MATCH(G271,AU18:AX18,0),0) so this problem has been diverted. Thanks "T. Valko" wrote: Here's a small sample file: http://cjoint.com/?hmtvfq5I7N Notice how the the names in the Type drop down list *match* the column headers of the table. -- Biff Microsoft Excel MVP "Excluxe" wrote in message ... To add on to what I just wrote is it possible to use a hlookup to choose to column with the correct pricelist and then vlookup to match the size to the price? "Toppers" wrote: Create a table as below: as the data appears to be the same for Spiral..." and "Steel ..." you only need columns A & B : C is redundant. As noted by other respondents, the table elements don't match: I've just copied as you had them. On Sheet1 (change in formula if not this sheet) A B C 6x6x4 7.35 7.35 8x8x4 9.05 9.05 10x10x4 12.70 12.70 12x12x4 14.35 14.35 14x14x4 20.50 20.50 16x16x4 32.50 32.50 18x18x4 41.00 41.00 20x20x4 44.50 44.50 24x24x4 50.00 50.00 53.50 53.50 60.00 Then ... =IF(OR(L74="Spiral Preformed steel galv straight lengths",L74="Steel PVC coated both sides straight lengths"),IF(ISNA(VLOOKUP(G47,Sheet1!A:B,2,0)),"No match",VLOOKUP(G47,Sheet1!A:B,2,0)),"") IF L74 can only be the above values, the test for L74 is also redundant. HTH "Greg Wilson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUM formula not working | Excel Discussion (Misc queries) | |||
Formula not working for me now | Excel Worksheet Functions | |||
Formula is not working | Excel Discussion (Misc queries) | |||
formula not working | Excel Discussion (Misc queries) | |||
Formula not working | Excel Worksheet Functions |