| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hardcore formula heroes - Combination Vlookup and multiple IF form
Put simply I am trying to create a formula in TAB 1, that if I enter a 'Part
Number' in cell A1, Vlookup will return its description in cell A2 (no problems so far), and then if I enter a quantity in A3, cell A4 will identify the correct band from the following ranges ,25, 26-100, 101-500, 501-1000 or 1000 and return the correct unit price for the part number and quantity break - which is identified and held in a separate table (on TAB 2), alongside the Part Number and Descriptions. Martin |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hardcore formula heroes - Combination Vlookup and multiple IF form
How is your pricing portion set up? Do you have quantities down the column,
and part numbers as row headers? This would be an easy way to do it. Because, then, no matter the quantity discounts, as long as you set up your table properly, you'll be good. I.E.: Part1 Part2 Part3 0 25.00 75.00 5.25 25 23.75 75.00 5.25 30 23.75 67.50 5.25 50 23.75 67.50 4.95 Basically, in this scenario, Part1 is $25.00, unless they order more than 25, Part2 is $75.00, unless they order 30 or more, and Part3 is $5.25 unless they order 50 or more. The key is to ensure the 'quantities' down the left column are in ascending order, and starting with 0. So your lookup would be: =VLOOKUP(qty,TblPrice!$A$1:$D$5,MATCH(part#,TblPri ce!A1:D1,0),1) This assumes your pricing table is a separate tab. Note the last '1' in the VLOOKUP, this will allow it to search for the best 'qty' in left column, without going over. Hope this helps. -- John C "Martin Panter" wrote: Put simply I am trying to create a formula in TAB 1, that if I enter a 'Part Number' in cell A1, Vlookup will return its description in cell A2 (no problems so far), and then if I enter a quantity in A3, cell A4 will identify the correct band from the following ranges ,25, 26-100, 101-500, 501-1000 or 1000 and return the correct unit price for the part number and quantity break - which is identified and held in a separate table (on TAB 2), alongside the Part Number and Descriptions. Martin |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hardcore formula heroes - Combination Vlookup and multiple IF
John,
Unfortunatley not. The reference table in TAB 2 is set up (read only), as follows Part No Description 0-25, 26-100, 101-500, 501-1000, 1000 AAAAA Widget $5.00 $4.00 $3.00 $2.00 $1.00 BBBBBB Gromet $9.00 $8.00 $7.00 $6.00 $5.00 etc. Any suggestions? -- Martin "John C" wrote: How is your pricing portion set up? Do you have quantities down the column, and part numbers as row headers? This would be an easy way to do it. Because, then, no matter the quantity discounts, as long as you set up your table properly, you'll be good. I.E.: Part1 Part2 Part3 0 25.00 75.00 5.25 25 23.75 75.00 5.25 30 23.75 67.50 5.25 50 23.75 67.50 4.95 Basically, in this scenario, Part1 is $25.00, unless they order more than 25, Part2 is $75.00, unless they order 30 or more, and Part3 is $5.25 unless they order 50 or more. The key is to ensure the 'quantities' down the left column are in ascending order, and starting with 0. So your lookup would be: =VLOOKUP(qty,TblPrice!$A$1:$D$5,MATCH(part#,TblPri ce!A1:D1,0),1) This assumes your pricing table is a separate tab. Note the last '1' in the VLOOKUP, this will allow it to search for the best 'qty' in left column, without going over. Hope this helps. -- John C "Martin Panter" wrote: Put simply I am trying to create a formula in TAB 1, that if I enter a 'Part Number' in cell A1, Vlookup will return its description in cell A2 (no problems so far), and then if I enter a quantity in A3, cell A4 will identify the correct band from the following ranges ,25, 26-100, 101-500, 501-1000 or 1000 and return the correct unit price for the part number and quantity break - which is identified and held in a separate table (on TAB 2), alongside the Part Number and Descriptions. Martin |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hardcore formula heroes - Combination Vlookup and multiple IF
=IF(OR(part#="",qty=""),"",VLOOKUP(part#,Tbl_Parts ,LOOKUP(qty,{0,3;26,4;101,5;501,6;1001,7}),FALSE))
Since all qty discounts break at the same quantity, you could do something like above. I check to ensure first that I have a part number and a quantity entered. Then, using the part number, I look up the price. The LOOKUP determines which column I pull the pricing from. Hope this helps. -- John C "Martin Panter" wrote: John, Unfortunatley not. The reference table in TAB 2 is set up (read only), as follows Part No Description 0-25, 26-100, 101-500, 501-1000, 1000 AAAAA Widget $5.00 $4.00 $3.00 $2.00 $1.00 BBBBBB Gromet $9.00 $8.00 $7.00 $6.00 $5.00 etc. Any suggestions? -- Martin "John C" wrote: How is your pricing portion set up? Do you have quantities down the column, and part numbers as row headers? This would be an easy way to do it. Because, then, no matter the quantity discounts, as long as you set up your table properly, you'll be good. I.E.: Part1 Part2 Part3 0 25.00 75.00 5.25 25 23.75 75.00 5.25 30 23.75 67.50 5.25 50 23.75 67.50 4.95 Basically, in this scenario, Part1 is $25.00, unless they order more than 25, Part2 is $75.00, unless they order 30 or more, and Part3 is $5.25 unless they order 50 or more. The key is to ensure the 'quantities' down the left column are in ascending order, and starting with 0. So your lookup would be: =VLOOKUP(qty,TblPrice!$A$1:$D$5,MATCH(part#,TblPri ce!A1:D1,0),1) This assumes your pricing table is a separate tab. Note the last '1' in the VLOOKUP, this will allow it to search for the best 'qty' in left column, without going over. Hope this helps. -- John C "Martin Panter" wrote: Put simply I am trying to create a formula in TAB 1, that if I enter a 'Part Number' in cell A1, Vlookup will return its description in cell A2 (no problems so far), and then if I enter a quantity in A3, cell A4 will identify the correct band from the following ranges ,25, 26-100, 101-500, 501-1000 or 1000 and return the correct unit price for the part number and quantity break - which is identified and held in a separate table (on TAB 2), alongside the Part Number and Descriptions. Martin |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hardcore formula heroes - Combination Vlookup and multiple IF
Hmmm...... I get the Vlookup and Lookup bit, but still unsure on the
(OR(A3="C3"),"". As this is a formula that will be copied over many cells I'm not sure if I have translated the syntax correctly. Intitial result returned is '#NAME?' Taken straight from the sheet where A3 is the part number and C3 is the variable quantity... =IF(OR(A3="C3"),"",VLOOKUP(A3,'EMEA SRP'!A:H,LOOKUP(qty,{0,4;26,4;101,6;501,7;1001,8}) ,FALSE)) -- Martin "John C" wrote: =IF(OR(part#="",qty=""),"",VLOOKUP(part#,Tbl_Parts ,LOOKUP(qty,{0,3;26,4;101,5;501,6;1001,7}),FALSE)) Since all qty discounts break at the same quantity, you could do something like above. I check to ensure first that I have a part number and a quantity entered. Then, using the part number, I look up the price. The LOOKUP determines which column I pull the pricing from. Hope this helps. -- John C "Martin Panter" wrote: John, Unfortunatley not. The reference table in TAB 2 is set up (read only), as follows Part No Description 0-25, 26-100, 101-500, 501-1000, 1000 AAAAA Widget $5.00 $4.00 $3.00 $2.00 $1.00 BBBBBB Gromet $9.00 $8.00 $7.00 $6.00 $5.00 etc. Any suggestions? -- Martin "John C" wrote: How is your pricing portion set up? Do you have quantities down the column, and part numbers as row headers? This would be an easy way to do it. Because, then, no matter the quantity discounts, as long as you set up your table properly, you'll be good. I.E.: Part1 Part2 Part3 0 25.00 75.00 5.25 25 23.75 75.00 5.25 30 23.75 67.50 5.25 50 23.75 67.50 4.95 Basically, in this scenario, Part1 is $25.00, unless they order more than 25, Part2 is $75.00, unless they order 30 or more, and Part3 is $5.25 unless they order 50 or more. The key is to ensure the 'quantities' down the left column are in ascending order, and starting with 0. So your lookup would be: =VLOOKUP(qty,TblPrice!$A$1:$D$5,MATCH(part#,TblPri ce!A1:D1,0),1) This assumes your pricing table is a separate tab. Note the last '1' in the VLOOKUP, this will allow it to search for the best 'qty' in left column, without going over. Hope this helps. -- John C "Martin Panter" wrote: Put simply I am trying to create a formula in TAB 1, that if I enter a 'Part Number' in cell A1, Vlookup will return its description in cell A2 (no problems so far), and then if I enter a quantity in A3, cell A4 will identify the correct band from the following ranges ,25, 26-100, 101-500, 501-1000 or 1000 and return the correct unit price for the part number and quantity break - which is identified and held in a separate table (on TAB 2), alongside the Part Number and Descriptions. Martin |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hardcore formula heroes - Combination Vlookup and multiple IF
where is your comma? and double quotes?
OR(A3="",C3="") And if you are copying down, this will change from A3="",C3="" to A4="",C4="" and A5="",C5="" etc. But that is A3="" (note 2 double quotes), then a comma, then C3="" Hope that helps. -- John C "Martin Panter" wrote: Hmmm...... I get the Vlookup and Lookup bit, but still unsure on the (OR(A3="C3"),"". As this is a formula that will be copied over many cells I'm not sure if I have translated the syntax correctly. Intitial result returned is '#NAME?' Taken straight from the sheet where A3 is the part number and C3 is the variable quantity... =IF(OR(A3="C3"),"",VLOOKUP(A3,'EMEA SRP'!A:H,LOOKUP(qty,{0,4;26,4;101,6;501,7;1001,8}) ,FALSE)) -- Martin "John C" wrote: =IF(OR(part#="",qty=""),"",VLOOKUP(part#,Tbl_Parts ,LOOKUP(qty,{0,3;26,4;101,5;501,6;1001,7}),FALSE)) Since all qty discounts break at the same quantity, you could do something like above. I check to ensure first that I have a part number and a quantity entered. Then, using the part number, I look up the price. The LOOKUP determines which column I pull the pricing from. Hope this helps. -- John C "Martin Panter" wrote: John, Unfortunatley not. The reference table in TAB 2 is set up (read only), as follows Part No Description 0-25, 26-100, 101-500, 501-1000, 1000 AAAAA Widget $5.00 $4.00 $3.00 $2.00 $1.00 BBBBBB Gromet $9.00 $8.00 $7.00 $6.00 $5.00 etc. Any suggestions? -- Martin "John C" wrote: How is your pricing portion set up? Do you have quantities down the column, and part numbers as row headers? This would be an easy way to do it. Because, then, no matter the quantity discounts, as long as you set up your table properly, you'll be good. I.E.: Part1 Part2 Part3 0 25.00 75.00 5.25 25 23.75 75.00 5.25 30 23.75 67.50 5.25 50 23.75 67.50 4.95 Basically, in this scenario, Part1 is $25.00, unless they order more than 25, Part2 is $75.00, unless they order 30 or more, and Part3 is $5.25 unless they order 50 or more. The key is to ensure the 'quantities' down the left column are in ascending order, and starting with 0. So your lookup would be: =VLOOKUP(qty,TblPrice!$A$1:$D$5,MATCH(part#,TblPri ce!A1:D1,0),1) This assumes your pricing table is a separate tab. Note the last '1' in the VLOOKUP, this will allow it to search for the best 'qty' in left column, without going over. Hope this helps. -- John C "Martin Panter" wrote: Put simply I am trying to create a formula in TAB 1, that if I enter a 'Part Number' in cell A1, Vlookup will return its description in cell A2 (no problems so far), and then if I enter a quantity in A3, cell A4 will identify the correct band from the following ranges ,25, 26-100, 101-500, 501-1000 or 1000 and return the correct unit price for the part number and quantity break - which is identified and held in a separate table (on TAB 2), alongside the Part Number and Descriptions. Martin |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hardcore formula heroes - Combination Vlookup and multiple IF
WOO HOOOOOOOOOOOOOOO
TOP MAN........... -- Martin "John C" wrote: where is your comma? and double quotes? OR(A3="",C3="") And if you are copying down, this will change from A3="",C3="" to A4="",C4="" and A5="",C5="" etc. But that is A3="" (note 2 double quotes), then a comma, then C3="" Hope that helps. -- John C "Martin Panter" wrote: Hmmm...... I get the Vlookup and Lookup bit, but still unsure on the (OR(A3="C3"),"". As this is a formula that will be copied over many cells I'm not sure if I have translated the syntax correctly. Intitial result returned is '#NAME?' Taken straight from the sheet where A3 is the part number and C3 is the variable quantity... =IF(OR(A3="C3"),"",VLOOKUP(A3,'EMEA SRP'!A:H,LOOKUP(qty,{0,4;26,4;101,6;501,7;1001,8}) ,FALSE)) -- Martin "John C" wrote: =IF(OR(part#="",qty=""),"",VLOOKUP(part#,Tbl_Parts ,LOOKUP(qty,{0,3;26,4;101,5;501,6;1001,7}),FALSE)) Since all qty discounts break at the same quantity, you could do something like above. I check to ensure first that I have a part number and a quantity entered. Then, using the part number, I look up the price. The LOOKUP determines which column I pull the pricing from. Hope this helps. -- John C "Martin Panter" wrote: John, Unfortunatley not. The reference table in TAB 2 is set up (read only), as follows Part No Description 0-25, 26-100, 101-500, 501-1000, 1000 AAAAA Widget $5.00 $4.00 $3.00 $2.00 $1.00 BBBBBB Gromet $9.00 $8.00 $7.00 $6.00 $5.00 etc. Any suggestions? -- Martin "John C" wrote: How is your pricing portion set up? Do you have quantities down the column, and part numbers as row headers? This would be an easy way to do it. Because, then, no matter the quantity discounts, as long as you set up your table properly, you'll be good. I.E.: Part1 Part2 Part3 0 25.00 75.00 5.25 25 23.75 75.00 5.25 30 23.75 67.50 5.25 50 23.75 67.50 4.95 Basically, in this scenario, Part1 is $25.00, unless they order more than 25, Part2 is $75.00, unless they order 30 or more, and Part3 is $5.25 unless they order 50 or more. The key is to ensure the 'quantities' down the left column are in ascending order, and starting with 0. So your lookup would be: =VLOOKUP(qty,TblPrice!$A$1:$D$5,MATCH(part#,TblPri ce!A1:D1,0),1) This assumes your pricing table is a separate tab. Note the last '1' in the VLOOKUP, this will allow it to search for the best 'qty' in left column, without going over. Hope this helps. -- John C "Martin Panter" wrote: Put simply I am trying to create a formula in TAB 1, that if I enter a 'Part Number' in cell A1, Vlookup will return its description in cell A2 (no problems so far), and then if I enter a quantity in A3, cell A4 will identify the correct band from the following ranges ,25, 26-100, 101-500, 501-1000 or 1000 and return the correct unit price for the part number and quantity break - which is identified and held in a separate table (on TAB 2), alongside the Part Number and Descriptions. Martin |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hardcore formula heroes - Combination Vlookup and multiple IF
Thanks for the feedback, please remember to check YES below that your
question is resolved so that others know it has been answered. -- John C "Martin Panter" wrote: WOO HOOOOOOOOOOOOOOO TOP MAN........... -- Martin "John C" wrote: where is your comma? and double quotes? OR(A3="",C3="") And if you are copying down, this will change from A3="",C3="" to A4="",C4="" and A5="",C5="" etc. But that is A3="" (note 2 double quotes), then a comma, then C3="" Hope that helps. -- John C "Martin Panter" wrote: Hmmm...... I get the Vlookup and Lookup bit, but still unsure on the (OR(A3="C3"),"". As this is a formula that will be copied over many cells I'm not sure if I have translated the syntax correctly. Intitial result returned is '#NAME?' Taken straight from the sheet where A3 is the part number and C3 is the variable quantity... =IF(OR(A3="C3"),"",VLOOKUP(A3,'EMEA SRP'!A:H,LOOKUP(qty,{0,4;26,4;101,6;501,7;1001,8}) ,FALSE)) -- Martin "John C" wrote: =IF(OR(part#="",qty=""),"",VLOOKUP(part#,Tbl_Parts ,LOOKUP(qty,{0,3;26,4;101,5;501,6;1001,7}),FALSE)) Since all qty discounts break at the same quantity, you could do something like above. I check to ensure first that I have a part number and a quantity entered. Then, using the part number, I look up the price. The LOOKUP determines which column I pull the pricing from. Hope this helps. -- John C "Martin Panter" wrote: John, Unfortunatley not. The reference table in TAB 2 is set up (read only), as follows Part No Description 0-25, 26-100, 101-500, 501-1000, 1000 AAAAA Widget $5.00 $4.00 $3.00 $2.00 $1.00 BBBBBB Gromet $9.00 $8.00 $7.00 $6.00 $5.00 etc. Any suggestions? -- Martin "John C" wrote: How is your pricing portion set up? Do you have quantities down the column, and part numbers as row headers? This would be an easy way to do it. Because, then, no matter the quantity discounts, as long as you set up your table properly, you'll be good. I.E.: Part1 Part2 Part3 0 25.00 75.00 5.25 25 23.75 75.00 5.25 30 23.75 67.50 5.25 50 23.75 67.50 4.95 Basically, in this scenario, Part1 is $25.00, unless they order more than 25, Part2 is $75.00, unless they order 30 or more, and Part3 is $5.25 unless they order 50 or more. The key is to ensure the 'quantities' down the left column are in ascending order, and starting with 0. So your lookup would be: =VLOOKUP(qty,TblPrice!$A$1:$D$5,MATCH(part#,TblPri ce!A1:D1,0),1) This assumes your pricing table is a separate tab. Note the last '1' in the VLOOKUP, this will allow it to search for the best 'qty' in left column, without going over. Hope this helps. -- John C "Martin Panter" wrote: Put simply I am trying to create a formula in TAB 1, that if I enter a 'Part Number' in cell A1, Vlookup will return its description in cell A2 (no problems so far), and then if I enter a quantity in A3, cell A4 will identify the correct band from the following ranges ,25, 26-100, 101-500, 501-1000 or 1000 and return the correct unit price for the part number and quantity break - which is identified and held in a separate table (on TAB 2), alongside the Part Number and Descriptions. Martin |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula help - possible vlookup/hlookup combination with IF or oth | Excel Worksheet Functions | |||
| Please Help!! Vlookup and combination formula | Excel Worksheet Functions | |||
| Formula help: VLOOKUP in a combination... | Excel Discussion (Misc queries) | |||
| Combination of H & Vlookup?? | Excel Worksheet Functions | |||
| "combination drop-down edit " form activation in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
