edit my index match Formula to say if SKU has the word Multi4 or Multi3 etc times result by that number?

[tonywatsonhelp]

Hi everyone,
I've got a bit of a problem
I do an index match of my sku's to tell me my stock levels from my weekly CSV readout.

I put the data into sheet "CSV" the simply have a formula in sheet "Stock Level" that index matches the Sku in "D6" (for example) with the data in sheet "CSV"

CSV column K holds the stock levels
CSV column C holds the SKU's

the formula is

=index('CSV'!$K:$K,match(D139,'CSV'!$C:$C,0))

The problem I have is some SKU's are for multi packs so the volue sold needs to be multiplied by the Multipack Number,
the multi pack number is show in the SKU if its multi pack like this

AAAA-AA-AAA-3PK-AAA for 3 pack
or
AAAA-AA-AAA-4PK-AAA for 4 pack etc.

if there is no multi pack 3PK is replaced by it size 10mg for example

so what I need is a way to say in the formula if this sku after the 3rd "-" contains "*PK" the times the lookup value by the number??

any ideas how I can do this??

thanks

Tony_{^{<strike>
</strike>}}

 

[etaf]

is the sku always the same number of characters
ie
pk is always char 14/15

=mid(cell, 14, 2 )
=MID(A2,14,2)

then =mid(cell, 13, 1 )*1
gives the number

 

[tonywatsonhelp]

Hi etaf, no it does change but its always after the 3rd "-"

 

[etaf]

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),3))+2,2)
will find the 3rd occurrence of -
then you can use

then use the index in an IF to extract
A if it = PK and then if it does * by the pack number
Just not sure what you want to multiple by 3 or 4

=IF(MID(index('CSV'!$K:$K,match(D139,'CSV'!$C:$C,0)),FIND(CHAR(1),SUBSTITUTE(index('CSV'!$K:$K,match(D139,'CSV'!$C:$C,0)),"-",CHAR(1),3))+2,2)="PK", MID(index('CSV'!$K:$K,match(D139,'CSV'!$C:$C,0)),FIND(CHAR(1),SUBSTITUTE(index('CSV'!$K:$K,match(D139,'CSV'!$C:$C,0)),"-",CHAR(1),3))+1,1),"n")

is it the value in K you want to X by 3 or 4
and if no PK
then what do you want to X the value by 1

i have just put N for is PK is not found

 

[etaf]

Excel Workbook
	F	G	H	I	J
10	Value to lookup	result
11	1	3		1	1123-123-123-3pk-ccccc
12				2	1123-123-123-3md-ccccc
13				3	1123-123-123-3ch-ccccc
14				4	1123-123-123-3pk-ccccc
Sheet1

 

[tonywatsonhelp]

thank you etaf for you help,
that is a fantastic formula

Tony

 

[etaf]

you are welcome
i suspect one of the more experienced members may be able to provide a simple solution , but thats all i can think of