If, Or, and

[AHMED]

Hello,

I need to arrange data in Microsoft excel 2007 but unable to get it through formula, Kindly help me to finalize my work. the situation is as under.

I use four type of codes in "CELL A1" RB or RG or RM or RL
and then
I use Sizes variation in "CELL B1" S00M or S01M or S02M or S03M belongs to gender "baby girl" or "baby boy"
and if Sizes Variation in "CELL B1" is S01Y or S02Y or S04Y, it belongs to gender "kids girl" or kids boy"
and if Sizes variation in "CELL B1" is S28 or S32 or S34 or S-M-L-XL, is belongs to gender "ladies" or "men"

Now in "CELL C1" the formula CELL
if "CELL A1" is RB and "CELL B1" sizes S00M then It will come in "CELL C1" baby boy
if "CELL A1" is RB and "CELL B1" sizes S01Y then It will come in "CELL C1" kids boy

if "CELL A1" is RB and "CELL B1" sizes S01Y then It will come in "CELL C1" kids boy
if "CELL A1" is RB and "CELL B1" sizes S01Y then It will come in "CELL C1" kids boy

if "CELL A1" is RG and "CELL B1" sizes S01Y then It will come in "CELL C1" baby girl
if "CELL A1" is RG and "CELL B1" sizes S01Y then It will come in "CELL C1" kids girl

if "CELL A1" is RM and "CELL B1" sizes S28 or XXL then It will come in "CELL C1" men

if "CELL A1" is RL and "CELL B1" sizes S28 or XXL then It will come in "CELL C1" ladies

kindly suggest the formula for CELL C1

 

[Amy @ German Pearls]

This should work in C1. Then just copy down for the other cells. If the cell returns ERROR you'd need to check something.

=IF(A1="RM","mens",IF(A1="RL","ladies",IF(AND(A1="RB",ISNUMBER(SEARCH("Y",B1))),"kids boy",IF(AND(A1="RB",ISNUMBER(SEARCH("M",B1))),"baby boy",IF(AND(A1="RG",ISNUMBER(SEARCH("Y",B1))),"kids girl",IF(AND(A1="RG",ISNUMBER(SEARCH("M",B1))),"baby girl","ERROR"))))))

Amy

 

[AHMED]

Thanks Amy, But I need to acknowledge you a one more thing, hope after reviewing you can provide new formula.

=IF(A1="RM","mens",IF(A1="RL","ladies",
this work perfect,

the issue comes with kids and babies,

below portion of formula changes require as do not mention ISNUMBER(SEARCH("M",B1) because I need to put all sizes range instead of "M", I need something like this ISNUMBER(SEARCH("S00M" & "S01M" & "S02M" & S01Y and so on in "B1" for baby boy and then keep continue for baby girl, kids boy and kids girls, basically I need to put sizes range myself in formula that CELL C1 will deduct what to come.

IF(AND(A1="RB",ISNUMBER(SEARCH("Y",B1))),"kids boy",IF(AND(A1="RB",ISNUMBER(SEARCH("M",B1))),"baby boy",IF(AND(A1="RG",ISNUMBER(SEARCH("Y",B1))),"kids girl",IF(AND(A1="RG",ISNUMBER(SEARCH("M",B1))),"baby girl","ERROR"))))))

hope you will understand what i am looking forward.

Highly appreciate for your as usual supportive response...


CODE CODE SIZE GENDER GENDER
RB RG S00M Bbaby Gbaby
RB RG S01M Bbaby Gbaby
RB RG S03M Bbaby Gbaby
RB RG S06M Bbaby Gbaby
RB RG S12M Bbaby Gbaby
RB RG S18M Bbaby Gbaby
RB RG S01Y Bbaby Gbaby
RB RG S02Y Bbaby Gbaby
RB RG S03Y Bbaby Gbaby
RB RG S04Y Bbaby Gbaby

RB RG S06Y Bkids Gkids
RB RG S08Y Bkids Gkids
RB RG S10Y Bkids Gkids
RB RG S12Y Bkids Gkids
RB RG S14Y Bkids Gkids

 

[AHMED]

This should work in C1. Then just copy down for the other cells. If the cell returns ERROR you'd need to check something.

=IF(A1="RM","mens",IF(A1="RL","ladies",IF(AND(A1="RB",ISNUMBER(SEARCH("Y",B1))),"kids boy",IF(AND(A1="RB",ISNUMBER(SEARCH("M",B1))),"baby boy",IF(AND(A1="RG",ISNUMBER(SEARCH("Y",B1))),"kids girl",IF(AND(A1="RG",ISNUMBER(SEARCH("M",B1))),"baby girl","ERROR"))))))

Amy

Thanks Amy, But I need to acknowledge you a one more thing, hope after reviewing you can provide new formula.
=IF(A1="RM","mens",IF(A1="RL","ladies",
this work perfect,

the issue comes with kids and babies,

below portion of formula changes require as do not mention ISNUMBER(SEARCH("M",B1) because I need to put all sizes range instead of "M", I need something like this ISNUMBER(SEARCH("S00M" & "S01M" & "S02M" & S01Y and so on in "B1" for baby boy and then keep continue for baby girl, kids boy and kids girls, basically I need to put sizes range myself in formula that CELL C1 will deduct what to come.

IF(AND(A1="RB",ISNUMBER(SEARCH("Y",B1))),"kids boy",IF(AND(A1="RB",ISNUMBER(SEARCH("M",B1))),"baby boy",IF(AND(A1="RG",ISNUMBER(SEARCH("Y",B1))),"kids girl",IF(AND(A1="RG",ISNUMBER(SEARCH("M",B1))),"baby girl","ERROR"))))))

hope you will understand what i am looking forward.

Highly appreciate for your as usual supportive response...


CODE CODE SIZE GENDER GENDER
RB RG S00M Bbaby Gbaby
RB RG S01M Bbaby Gbaby
RB RG S03M Bbaby Gbaby
RB RG S06M Bbaby Gbaby
RB RG S12M Bbaby Gbaby
RB RG S18M Bbaby Gbaby
RB RG S01Y Bbaby Gbaby
RB RG S02Y Bbaby Gbaby
RB RG S03Y Bbaby Gbaby
RB RG S04Y Bbaby Gbaby

RB RG S06Y Bkids Gkids
RB RG S08Y Bkids Gkids
RB RG S10Y Bkids Gkids
RB RG S12Y Bkids Gkids
RB RG S14Y Bkids Gkids

 

[Amy @ German Pearls]

Based on this if/then logic may not be the cleanest choice. I'm thinking vlookup may be a better option.
Here's a method that might work for you.

1. List all options into a long list
2. In the column all the way to the left put the formula =I1&J1 (to join the two options into one)
3. Use the vlookup in your cell, the criteria for the vlookup would be a1&b1

Hope this helps!
Amy

Ref: https://exceljet.net/formula/vlookup-with-two-or-more-criteria

 

[AHMED]

Based on this if/then logic may not be the cleanest choice. I'm thinking vlookup may be a better option.
Here's a method that might work for you.

1. List all options into a long list
2. In the column all the way to the left put the formula =I1&J1 (to join the two options into one)
3. Use the vlookup in your cell, the criteria for the vlookup would be a1&b1

View attachment 213

Hope this helps!
Amy

Ref: https://exceljet.net/formula/vlookup-with-two-or-more-criteria

GREAT...
It is working good, but; when I drag down the formula, it comes change in cell numbers, for example in cell C1 it is VLOOKUP(B8&H8,V3:Y32,4) and when i drag formula to C2 it comes like this VLOOKUP(B9&H9,V4:Y33,4)

RED formula portion changes when I drag down... what to do now please?