|
vlookup
I need to update pricing based on different scenarios for different product
groups. I have one spreadsheet that has each item, its product group and 3 levels of current pricing . I have another that lists each product group and the factors for the new pricing. Im trying to use VLOOKUP to use the Product Group to update the pricing based on the corresponding factor, but I cant seem to find a way to use these factors - as they are treated as text. I have 31,000 items and 21 product groups. EXAMPLE: ITEM NewPrice1 NewPrice2 NewPrice3 Price1 Price2 Price3 Product Grp ABC ? ? ? 5.00 3.00 3.00 1001 AB5 7.00 7.00 6.50 1021 PRODUCT GROUP NewPrice1 NewPrice2 NewPrice3 1001 Price2*.85 Price2*1.00 Price2*1.00 1002 Price3*.95 Price3*.85 Price2*1.00 |
vlookup
You might find it easier to create your second table as below with 2 columns
per price: PRODUCT NewPrice1 NewPrice2 NewPrice3 1001 Price2 0.85 Price2 1.00 Price2 1.00 1002 Price3 0.95 Price3 0.85 Price2 1.00 You can use a combination of INDEX anf VLOOKUP to get the data. in example below, ITEM is row1, PRODUCT is row 6. Columns are from A onwards ITEM NewPrice1 NewPrice2 NewPrice3 Price1 Price2 Price3 Product ABC 2.55 5.00 3.00 3.00 1001 AB5 7.00 7.00 6.50 1021 PRODUCT NewPrice1 NewPrice2 NewPrice3 1001 Price2 0.85 Price2 1.00 Price2 1.00 1002 Price3 0.95 Price3 0.85 Price2 1.00 Formula to get the 2.55 figure is: =(INDEX($E$1:$G$3,MATCH($H2,$A$7:$A$8,0)+1,MATCH($ B$7,$E$1:$G$1,0)))*VLOOKUP($H2,$A$7:$C$7,3,0) Hope this helps. "lp" wrote: I need to update pricing based on different scenarios for different product groups. I have one spreadsheet that has each item, its product group and 3 levels of current pricing . I have another that lists each product group and the factors for the new pricing. Im trying to use VLOOKUP to use the Product Group to update the pricing based on the corresponding factor, but I cant seem to find a way to use these factors - as they are treated as text. I have 31,000 items and 21 product groups. EXAMPLE: ITEM NewPrice1 NewPrice2 NewPrice3 Price1 Price2 Price3 Product Grp ABC ? ? ? 5.00 3.00 3.00 1001 AB5 7.00 7.00 6.50 1021 PRODUCT GROUP NewPrice1 NewPrice2 NewPrice3 1001 Price2*.85 Price2*1.00 Price2*1.00 1002 Price3*.95 Price3*.85 Price2*1.00 |
vlookup
...minor correction ..
=(INDEX($E$1:$G$3,MATCH($H3,$A$7:$A$8,0)+1,MATCH($ B8,$E$1:$G$1,0)))*VLOOKUP($H3,$A$7:$G$8,3,0) "Toppers" wrote: You might find it easier to create your second table as below with 2 columns per price: PRODUCT NewPrice1 NewPrice2 NewPrice3 1001 Price2 0.85 Price2 1.00 Price2 1.00 1002 Price3 0.95 Price3 0.85 Price2 1.00 You can use a combination of INDEX anf VLOOKUP to get the data. in example below, ITEM is row1, PRODUCT is row 6. Columns are from A onwards ITEM NewPrice1 NewPrice2 NewPrice3 Price1 Price2 Price3 Product ABC 2.55 5.00 3.00 3.00 1001 AB5 7.00 7.00 6.50 1021 PRODUCT NewPrice1 NewPrice2 NewPrice3 1001 Price2 0.85 Price2 1.00 Price2 1.00 1002 Price3 0.95 Price3 0.85 Price2 1.00 Formula to get the 2.55 figure is: =(INDEX($E$1:$G$3,MATCH($H2,$A$7:$A$8,0)+1,MATCH($ B$7,$E$1:$G$1,0)))*VLOOKUP($H2,$A$7:$C$7,3,0) Hope this helps. "lp" wrote: I need to update pricing based on different scenarios for different product groups. I have one spreadsheet that has each item, its product group and 3 levels of current pricing . I have another that lists each product group and the factors for the new pricing. Im trying to use VLOOKUP to use the Product Group to update the pricing based on the corresponding factor, but I cant seem to find a way to use these factors - as they are treated as text. I have 31,000 items and 21 product groups. EXAMPLE: ITEM NewPrice1 NewPrice2 NewPrice3 Price1 Price2 Price3 Product Grp ABC ? ? ? 5.00 3.00 3.00 1001 AB5 7.00 7.00 6.50 1021 PRODUCT GROUP NewPrice1 NewPrice2 NewPrice3 1001 Price2*.85 Price2*1.00 Price2*1.00 1002 Price3*.95 Price3*.85 Price2*1.00 |
| All times are GMT +1. The time now is 02:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com