+ Reply to Thread
Results 1 to 6 of 6

Formula help for IF, OR, LEN(), LEFT()

  1. #1
    Registered User
    Join Date
    09-03-2009
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    24

    Formula help for IF, OR, LEN(), LEFT()

    Trying to use one formula for a variety logical tests. Is it possible to write:

    =IF(Logical Test, Value if True, Value if False)

    =If(A1=("ABC" OR "BCD" OR "CDE" OR "DEF"),("ABC"="123","BCD"="234","CDE"="345","DEF"="456"),"IT'S BROKEN")

    If you view the attached spreadsheet and see my progression and the end result it will probably make more sense. I also may need to use IF LEFT() for the final data I'm working with.

    I'm totally stumped on this one.

    Thx for any help. This forum is awesome!!!

    Running XP Pro/Excel 2003 (have access to 2007)
    Attached Files Attached Files

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula help for IF, OR, LEN(), LEFT()

    are you really working with these values or is that just an example?
    i suspect your real data is nothing like that. but for step 1
    =IF(ISNA(MATCH(A3,{"abc","bcd","cde","def"},0)),"it's broken",INDEX({123,234,345,456},MATCH(A3,{"abc","bcd","cde","def"},0)))
    as you can see you have to pue all values in the function
    so a beter approach would be to use a "data" sheet
    in a1 down put abc,cde and so on with the corresponding values in b1 down
    then use a look up of some sort
    say vlookup(a3,data!a1:a100,2,false)
    your working example doesnt include any examples that are "broken"
    Last edited by martindwilson; 09-05-2009 at 08:10 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    09-03-2009
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Formula help for IF, OR, LEN(), LEFT()

    The data is formatted exactly as I displayed in the "working data" section but the values are different. I download files from an FTP site every day that are formatted this way - I am trying to keep my manual process down to simply downloading new data sheets every day rather than to open, format and save as, etc.

    I can't provide my real working data as some of it is sensative but the examples I gave were very similar.

    Also,I didn't have any "broken" formulas because I didn't even know where to start writing the formula.

    Thx for the reply, I will test this right away.
    Last edited by shg; 09-05-2009 at 08:40 PM. Reason: deleted spurious quote

  4. #4
    Registered User
    Join Date
    09-03-2009
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Formula help for IF, OR, LEN(), LEFT()

    Here is another way to ask this:

    Can I string several of these along? Such that I could have a set of 22 different IF statements in a single cell/formula? I also have an external web query that pulls some of this information into excel which has XYZ and what it should equal

    =IF(A7="XYZ",IF(LEFT(C7,6)="RGLGLL","RGLGLL",C7),C7)

    The above works for me on a one-by one basis but I'd like to incorporate all 22 different variations of this into a single formula.

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Formula help for IF, OR, LEN(), LEFT()

    Hello winstontj,

    Excel 2003 is limited to 7 nested functions, although there are ways round that it's probably better to use some sort of table as Martin suggests.

    You say you have 22 variations....of what? is the "XYZ" part static with 22 variations on "RGLGLL"? or is it the other way round (or do they mix and match?)

    You can probably still use VLOOKUP even if you are examining the first 6 characters, i.e. you can use LEFT(C7,6) as the lookup value.

    If you want a more specific answer, rather than general advice, then you'll need to provide specific examples of what you want to do. Of course you can change the data but you need to be precise about the nature of the problem

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Formula help for IF, OR, LEN(), LEFT()

    How about this:

    =LOOKUP(A22;$A$11:$A$18;$B$11:$B$19)&LOOKUP(COUNTIF($A$21:A21;A22);{0;1;2;3;4;5};{"";"AA";"BB";"CC";"DD";"EE"})

    Book1(1).xls

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1