If, Or, and

Discussion in 'Excel' started by AHMED, Nov 6, 2016.

  1. AHMED

    AHMED

    Joined:
    Nov 5, 2016
    Messages:
    4
    Likes Received:
    0
    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
     
    AHMED, Nov 6, 2016
    #1
    1. Advertisements

  2. AHMED

    Amy @ German Pearls

    Joined:
    Feb 2, 2016
    Messages:
    25
    Likes Received:
    9
    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
     
    Amy @ German Pearls, Nov 6, 2016
    #2
    AHMED and Becky like this.
    1. Advertisements

  3. AHMED

    AHMED

    Joined:
    Nov 5, 2016
    Messages:
    4
    Likes Received:
    0
    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, Nov 9, 2016
    #3
  4. AHMED

    AHMED

    Joined:
    Nov 5, 2016
    Messages:
    4
    Likes Received:
    0
    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, Nov 9, 2016
    #4
  5. AHMED

    Amy @ German Pearls

    Joined:
    Feb 2, 2016
    Messages:
    25
    Likes Received:
    9
    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
    upload_2016-11-14_22-41-14.png

    Hope this helps!
    Amy

    Ref: https://exceljet.net/formula/vlookup-with-two-or-more-criteria
     
    Amy @ German Pearls, Nov 15, 2016
    #5
  6. AHMED

    AHMED

    Joined:
    Nov 5, 2016
    Messages:
    4
    Likes Received:
    0
    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?
     
    AHMED, Nov 15, 2016
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. chel
    Replies:
    0
    Views:
    524
  2. David92595
    Replies:
    1
    Views:
    737
    lcaretto
    Jul 25, 2012
  3. SDijk
    Replies:
    0
    Views:
    573
    SDijk
    Sep 26, 2012
  4. annbc
    Replies:
    0
    Views:
    490
    annbc
    Aug 26, 2013
  5. annbc

    mid and isnumber

    annbc, Aug 26, 2013, in forum: Excel
    Replies:
    1
    Views:
    567
    annbc
    Aug 26, 2013
  6. marste7

    Copy and Paste Formula

    marste7, Aug 27, 2013, in forum: Excel
    Replies:
    1
    Views:
    775
    XLPadawan
    Jun 16, 2016
  7. Hwalker1
    Replies:
    2
    Views:
    557
    XLPadawan
    Jun 13, 2016
  8. Desiree Stevenson
    Replies:
    0
    Views:
    200
    Desiree Stevenson
    Aug 4, 2016
Loading...