# Distinguish whether the next cell down is <20 OR a word in a different column is present..

1. ## Distinguish whether the next cell down is <20 OR a word in a different column is present..

Hello,

In column A I have employee ID numbers.
In column B i have an event list.

I would like column C to distinguish when...
...the word "Create" is in column B...
...whilst the number beside it is less than 20 in column A...
...whilst EITHER of the following two incidents occur:
1) the next cell down in column A is also less than 20 (it can be the same number as the cell above)
OR
2) The next word down in column B is either "Work", "People" or "Team".

so in the following example,
2 Create [POSTIVE RESULT]
6 Apple
will give a positive outcome.

also:
2 Create [POSITIVE RESULT]
35 People
will give a positibe outcome because of the "people" word

however,
2 Create
35 Apple
will not give a result

Thank you

2. ## Re: Distinguish whether the next cell down is <20 OR a word in a different column is prese

Try

=IF(AND(B2="Create",A2<20,OR(A3<20,ISNUMBER(MATCH(B3,{"People","Team","Work"},0)))),"Positive","")

3. ## Re: Distinguish whether the next cell down is <20 OR a word in a different column is prese

=IF(AND(B2="Create",A2<20,OR(B3={"People","Work","Team"},A3<20)),"Positive Result","")

4. ## Re: Distinguish whether the next cell down is <20 OR a word in a different column is prese

thanks to both of you.

5. ## Re: Distinguish whether the next cell down is <20 OR a word in a different column is prese

Nice daffodil,

I still forget on occasion that, used in the correct context, a simple OR condition will pass an array as effectively, and more simply, than other constructions.

Regards

6. ## Re: Distinguish whether the next cell down is <20 OR a word in a different column is prese

Thanks!

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

#### 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