CODEWITHSUNDEEP
excel
Rider72
Rider72

Reputation: 21

Excel If(and / If(or

I have this task:

Try to get a value from cells X-Y if Cell C contains one of for Text values (OR1, OR2, OR3, OR4)

I've tried if(or(C8754="OR1","OR2","OR3","OR4")X8754-Y8754," ")

But I keep getting a value error. Any ideas?

Upvotes: 2

Views: 63

Answers (2)

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3833

Note that in your case, you can (almost) set up your formula the way you originally intended, with the addition of the {} brackets:

=IF(OR(C8754={"OR1","OR2","OR3","OR4"}),X8754-Y8754," ")

If you are using hardcoded constants you can make a list of items like that, and Excel will attempt each of them in turn, to see if any of them equal the value in cell C8754. While this is technically an array of values, Excel does not consider this to be an 'Array Formula', so no other special treatment is required (look that up if you're interested, but not relevant here).

Upvotes: 0

Robin Gertenbach
Robin Gertenbach

Reputation: 10796

OR() does not return true if one element of a list of possible values equals the first one but rather returns true if at least one of the containing expressions is true.

Try:

=IF(OR(C8754="OR1",
       C8754="OR2",
       C8754="OR3",
       C8754="OR4"),
    X8754-Y8754,
    " ")

Upvotes: 1

Related Questions