Logical expression with two conditions.

[T.I.]

Hi experts,

I have a calculation sheet (attached). In E column formula determines the volume of bottles and returns respective value:

For example in column C volume: 250 ml = 20 pcs, 330 = 20 pcs, 1L = 12 pcs and etc. Also formula detects if there is no space between value and letters in C cells and returns the right value anyway.

But, sometimes I need volumes to return different values according to condition in cell A1.

For example if A1=UK then 250 ml = 12 pcs, and the other values should remain untouched, but if A1=US then 250 ml = 20 pcs. Is it possible to modify this formula?
Please advise.


Also, in column G I have a formula that works. It returns respective dimensions of the Cases according to the values from C and E columns. Is it possible to make it shorter somehow?
Thank you.

[José Augusto]

Try this formula in E2

Please Login or Register  to view this content.

"1L",12,8 means that 1 L is 12 pcs for US and 20 for UK

[T.I.]

Try this formula in E2

Please Login or Register  to view this content.

"1L",12,8 means that 1 L is 12 pcs for US and 20 for UK

Great! Thank you so much.

[T.I.]

Could you also advise about second formula in G column. It works, but I suspect it could be much more shorter:

Please Login or Register  to view this content.

[Bo_Ry]

Please try at

E2
=LOOKUP(-LOOKUP(,-(0&LEFT(C2,{1,2,3,4}))),{0,1,250,330},IF($A$1="us",{"",12,20,20},{"",12,12,20}))

G2
=IF(C2="","",IF(C2="1L","39X30X26",IF(C2="250 ml",IF(E2=20,"29X23X15","36X28X20"),"33X26X18")))

[T.I.]

Please try at

E2
=LOOKUP(-LOOKUP(,-(0&LEFT(C2,{1,2,3,4}))),{0,1,250,330},IF($A$1="us",{"",12,20,20},{"",12,12,20}))

G2
=IF(C2="","",IF(C2="1L","39X30X26",IF(C2="250 ml",IF(E2=20,"29X23X15","36X28X20"),"33X26X18")))

Absolutely superb! Many many thanks!

[T.I.]

Try this formula in E2

Please Login or Register  to view this content.

"1L",12,8 means that 1 L is 12 pcs for US and 20 for UK

Hi Jose, For some reasons I need to add to this formula 30 gr condition (marked in red)
=IF(TRIM(C2)<>C2,"Error",VLOOKUP(SUBSTITUTE(C2," ",""),{"","","";"1 L",12,8;"250ML",20,12;"330ML",20,12;"370gr",20,12;"30gr",40,40},2+(UPPER($A$1)="UK"),1))
But it doesn't work. Can you advise?

[José Augusto]

Try this
=IF(TRIM(C2)<>C2,"Error",VLOOKUP(SUBSTITUTE(C2," ",""),{"","","";"1 L",12,8;"250ML",20,12;"330ML",20,12;"370gr",20,12;"30gr",40,40},2+(UPPER($A$1)="UK"),0))

[T.I.]

Great and in fact so simple Thank you.