vlookup

[lp]

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. I’m trying to use VLOOKUP to use the
Product Group to update the pricing based on the corresponding factor, but I
can’t 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

 

[Toppers]

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.

 

[Toppers]

...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)