Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    hi all,

    i have the following data in a column
    RC
    R
    H3
    H1
    *
    MRC
    RC-T

    and i use the following formula
    =IF(LEFT(W11,1)="R","RE",IF(LEFT(W11,2)="RC","WO", IF(LEFT(W11,1)="H","NEW",IF(LEFT(W11,3)="*","UNDF" ,IF(LEFT(W11,3)="MRC","NEW",)))))

    the result i got is

    RE
    RE
    NEW
    NEW
    UNDF
    NEW
    RE


    and it does not seems to pick up the value for "RC" it set it to "RE" instead of "WO". any suggestions.


    dubdub
    TIA
    dubdub

  2. #2
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    =IF(LEFT(W11,2)="RC","WO",IF(LEFT(W11,1)="R","RE", IF(LEFT(W11,1)="H","NEW",IF(LEFT(W11,3)="*","UNDF" ,IF(LEFT(W11,3)="MRC","NEW",)))))

    You need to check the most "onerous" first, otherwise you get the first true answer
    eg
    If(left(W11,3)="MRC","xxx",left(W11,2)="MR","xx",l eft(W11,1)="M","x"

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Mdmackillop.

    dubdub
    TIA
    dubdub

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Why not make it simpler and just use a vlookup

    Place the following in F1:G7

    RC WO
    R RE
    H3 NEW
    H1 NEW
    * UNDF
    MRC NEW
    RC-T WO

    and then (assuming in column A) the following formula in column B and copy down

    =VLOOKUP(A1,$F$1:$G$7,2,FALSE)
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •