Value based on partial match

danwxm

New Member
Joined
Sep 16, 2014
Messages
3
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.

SKURRPSale PriceCost PriceSupplier Code
ABC-00001-848-S

<tbody>
</tbody>
10.00
5.002.00
00001848S

<tbody>
</tbody>
ABC-00001-848-M

<tbody>
</tbody>
10.005.002.00
00001848M

<tbody>
</tbody>
ABC-00001-848-L

<tbody>
</tbody>
10.005.002.00
00001848L

<tbody>
</tbody>
ABC-00001-P

<tbody>
</tbody>
9.005.000.00
00001P

<tbody>
</tbody>
ABC-RH-000052-001-S

<tbody>
</tbody>
20.0010.005.00
RH000052001S

<tbody>
</tbody>
ABC-RH-000052-001-M

<tbody>
</tbody>
20.0010.005.00
RH000052001M

<tbody>
</tbody>
ABC-RH-000052-P

<tbody>
</tbody>
19.0010.000.00
RH000052P

<tbody>
</tbody>
ABC-RH-000052-001-L

<tbody>
</tbody>
20.0010.005.00
RH000052001L

<tbody>
</tbody>
ABC-RH-000052-001-XL

<tbody>
</tbody>
20.0010.005.00
RH000052001XL

<tbody>
</tbody>
ABC-RH-000052-001-XXL

<tbody>
</tbody>
20.0010.005.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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Can you give sample data of the sheet where the RPP is looked up from or the vlookup formula that returns the RPP
 

danwxm

New Member
Joined
Sep 16, 2014
Messages
3
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 CodeTrade 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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Is this what you want?


Excel 2010
ABCDEFGH
1SKURRPSale PriceCost PriceSupplier CodeSupplier CodeTrade Price
2ABC-00001-848-S105200001848S00001848S2
3ABC-00001-848-M105200001848M00001848M2
4ABC-00001-848-L105200001848L00001848L2
5ABC-00001-P95200001PRH000052001S5
6ABC-RH-000052-001-S20105RH000052001SRH000052001M5
7ABC-RH-000052-001-M20105RH000052001MRH000052001L5
8ABC-RH-000052-P19105RH000052PRH000052001XL5
9ABC-RH-000052-001-L20105RH000052001LRH000052001XXL5
10ABC-RH-000052-001-XL20105RH000052001XL
11ABC-RH-000052-001-XXL20105RH000052001XXL
Sheet1
Cell Formulas
RangeFormula
D2=VLOOKUP(IF(RIGHT(E2,1)="P",LEFT(E2,LEN(E2)-1)&"*",E2),G$2:H$9,2,FALSE)
 

danwxm

New Member
Joined
Sep 16, 2014
Messages
3
Is this what you want?

Excel 2010
ABCDEFGH
1SKURRPSale PriceCost PriceSupplier CodeSupplier CodeTrade Price
2ABC-00001-848-S105200001848S00001848S2
3ABC-00001-848-M105200001848M00001848M2
4ABC-00001-848-L105200001848L00001848L2
5ABC-00001-P95200001PRH000052001S5
6ABC-RH-000052-001-S20105RH000052001SRH000052001M5
7ABC-RH-000052-001-M20105RH000052001MRH000052001L5
8ABC-RH-000052-P19105RH000052PRH000052001XL5
9ABC-RH-000052-001-L20105RH000052001LRH000052001XXL5
10ABC-RH-000052-001-XL20105RH000052001XL
11ABC-RH-000052-001-XXL20105RH000052001XXL

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

Worksheet Formulas
CellFormula
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,706
Messages
5,833,232
Members
430,197
Latest member
edeibold

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top