Value based on partial match

[danwxm]

Hi all,

New here and have a question regarding a price update I am trying to do.

I can perform a vlookup on a supplier price update to update the prices of the sub SKUs I have but because the Parent SKUs are slightly different in the sense that they just end with '-P' I cannot get these to update prices. Is there any way I can kind of tell excel that if the SKU ends in '-P' then to look for the first part of the SKU and return the same value as that? Please see the attached worksheet for an example.

SKU	RRP	Sale Price	Cost Price	Supplier Code
ABC-00001-848-S <tbody> </tbody>	10.00	5.00	2.00	00001848S <tbody> </tbody>
ABC-00001-848-M <tbody> </tbody>	10.00	5.00	2.00	00001848M <tbody> </tbody>
ABC-00001-848-L <tbody> </tbody>	10.00	5.00	2.00	00001848L <tbody> </tbody>
ABC-00001-P <tbody> </tbody>	9.00	5.00	0.00	00001P <tbody> </tbody>
ABC-RH-000052-001-S <tbody> </tbody>	20.00	10.00	5.00	RH000052001S <tbody> </tbody>
ABC-RH-000052-001-M <tbody> </tbody>	20.00	10.00	5.00	RH000052001M <tbody> </tbody>
ABC-RH-000052-P <tbody> </tbody>	19.00	10.00	0.00	RH000052P <tbody> </tbody>
ABC-RH-000052-001-L <tbody> </tbody>	20.00	10.00	5.00	RH000052001L <tbody> </tbody>
ABC-RH-000052-001-XL <tbody> </tbody>	20.00	10.00	5.00	RH000052001XL <tbody> </tbody>
ABC-RH-000052-001-XXL <tbody> </tbody>	20.00	10.00	5.00	RH000052001XXL <tbody> </tbody>

<tbody>
</tbody>

As you can see from the above the parent SKUs (ending in -P) have a different price to the sub SKUs because the vlookup used in the RRP column cannot find the SKU in the Supplier Code field as it is lacking the colour indicator (848 or 001) found in the supplier SKU.

Hopefully this all makes sense but if not then please ask me for more information!

Dan

 

[Andrew Poulsom]

Welcome to MrExcel.

What are you trying to look up and where?

 

[Momentman]

Can you give sample data of the sheet where the RPP is looked up from or the vlookup formula that returns the RPP

 

[danwxm]

Hi both,

I am unable to attach a file so I will build another table with the sample supplier price sheet that the previous table is looked up against

Supplier Code	Trade Price
00001848S <tbody> </tbody>	2.00
00001848M <tbody> </tbody>	2.00
00001848L <tbody> </tbody>	2.00
RH000052001S <tbody> </tbody>	5.00 <tbody> </tbody>
RH000052001M <tbody> </tbody>	5.00 <tbody> </tbody>
RH000052001L <tbody> </tbody>	5.00 <tbody> </tbody>
RH000052001XL <tbody> </tbody>	5.00 <tbody> </tbody>
RH000052001XXL <tbody> </tbody>	5.00 <tbody> </tbody>

<tbody>
</tbody>

As I said above, the previous table has vlookups set to find the cost price from the above table and then a formula working out the RRP and sale price from the cost price field.

The problem I have is that the parent SKUs will never be found in the supplier price update sheets as there is no further information other than Product Code (The SKUs are built as Supplier Code-Product Code-Colour Code-Size)

Dan

 

[Andrew Poulsom]

Is this what you want?

Excel 2010
	A	B	C	D	E	F	G	H
1	SKU	RRP	Sale Price	Cost Price	Supplier Code		Supplier Code	Trade Price
2	ABC-00001-848-S	10	5	2	00001848S		00001848S	2
3	ABC-00001-848-M	10	5	2	00001848M		00001848M	2
4	ABC-00001-848-L	10	5	2	00001848L		00001848L	2
5	ABC-00001-P	9	5	2	00001P		RH000052001S	5
6	ABC-RH-000052-001-S	20	10	5	RH000052001S		RH000052001M	5
7	ABC-RH-000052-001-M	20	10	5	RH000052001M		RH000052001L	5
8	ABC-RH-000052-P	19	10	5	RH000052P		RH000052001XL	5
9	ABC-RH-000052-001-L	20	10	5	RH000052001L		RH000052001XXL	5
10	ABC-RH-000052-001-XL	20	10	5	RH000052001XL
11	ABC-RH-000052-001-XXL	20	10	5	RH000052001XXL
Sheet1

Cell Formulas
Range	Formula
D2	=VLOOKUP(IF(RIGHT(E2,1)="P",LEFT(E2,LEN(E2)-1)&"*",E2),G$2:H$9,2,FALSE)

 

[danwxm]

Is this what you want?

Excel 2010

	A	B	C	D	E	F	G	H
1	SKU	RRP	Sale Price	Cost Price	Supplier Code		Supplier Code	Trade Price
2	ABC-00001-848-S	10	5	2	00001848S		00001848S	2
3	ABC-00001-848-M	10	5	2	00001848M		00001848M	2
4	ABC-00001-848-L	10	5	2	00001848L		00001848L	2
5	ABC-00001-P	9	5	2	00001P		RH000052001S	5
6	ABC-RH-000052-001-S	20	10	5	RH000052001S		RH000052001M	5
7	ABC-RH-000052-001-M	20	10	5	RH000052001M		RH000052001L	5
8	ABC-RH-000052-P	19	10	5	RH000052P		RH000052001XL	5
9	ABC-RH-000052-001-L	20	10	5	RH000052001L		RH000052001XXL	5
10	ABC-RH-000052-001-XL	20	10	5	RH000052001XL
11	ABC-RH-000052-001-XXL	20	10	5	RH000052001XXL

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =VLOOKUP(IF(RIGHT(E2,1)="P",LEFT(E2,LEN(E2)-1)&"*",E2),G$2:H$9,2,FALSE) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Where ABC-00001-848-L and ABC-00001-P are effectively the same product I want the RRP to be the same price rather than in this example the parent being a pound less than the sub SKUs if that makes sense? and at the same time the cost price on the parent SKU to stay as £0.

 

[Andrew Poulsom]

In what way isn't my formula returning the result you expect?