Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Do I use the OR function, or something else? (2000)

    The short version: How do I tell Excel that if a cell contains one of a group of values, it should perform some sort of operation? I can't seem to make OR work; am I on the wrong track?

    The long version: If a cell contains a specific value (A, B, C or D), I want Excel to indicate "yes", otherwise indicate "no". In quasi Excel-English it would look like this:
    =IF(A1=("A" or "B" or "C" or "D"), "yes","no")
    which, of course, doesn't work at all.

    When I try to use Excel language:
    =IF(A1=OR("A", "B", "C", "D"), "yes","no")
    I get an error. From what I can figure out, it will only tell me "yes" if A1 contains all four letters, but that doesn't make any sense. My brain hurts.

    How should I write the formula so that it will tell me if cell A1 contains any of those letters.

  2. #2
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do I use the OR function, or something else? (2000)

    I think you have your OR parameters messed up ..
    If you want this:
    IF(A1=OR("A", "B", "C", "D"), "yes","no")
    Try this:
    =IF (OR(A1="A",A1="B",A1="C",A1="D"),"YES","NO")

    kinda cumbersome, but that's how it works.


    --CAT

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Do I use the OR function, or something else? (2000)

    You can also use:
    <pre>=IF(ISERROR(FIND(A1,"A,B,C,D")),"No", "yes")</pre>

    for case sensitive

    Or for case insensitive:
    <pre>=IF(ISERROR(Search(A1,"A,B,C,D")),"No", "yes")</pre>


    This is easier to enter, if you have a lot of items to OR

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Do I use the OR function, or something else? (2000)

    This is another way (which is similar) to your original scheme
    <pre>=IF(ISNA(MATCH(A1,{"A","B","C","D"},FALSE))," No", "yes")</pre>


    Steve

Posting Permissions

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