Array lookup

Murtuza Posts 6 Registration date Wednesday September 4, 2013 Status Member Last seen September 7, 2013 - Sep 4, 2013 at 01:43 PM
 Blocked Profile - Sep 4, 2013 at 02:18 PM
I was wondring if there was a formula to do this in excel. I have the data in two columns, col a and col b
What I want excel to check first is if there are any values in col A thst are repeating themselves. If there are any then it should take all those values and check their adjacent cells in col B for further conditions.
So I could have my data listed like this
a 1
a 2
a 7
b 4
b 5
c 6
So in the first case the first condition is met as "a" repeats should now check all values correponding to "a" and if any one value is less than 2 and any other value is greater than 5 it should give me in the first case it should give me true as the value corresponding to first "a" is less than 2 and the value corresponfing to third "a" is greater than 5 .However for b it should return false as neither of the two conditions are met.Hope I have explained this well.Will appreciate any responses on this .

1 response

Blocked Profile
Sep 4, 2013 at 02:18 PM
Good Afternoon,

Can you nest an AND, OR with an IF?

So this would be in the C column.

=IF(AND(A1="a",OR(B1<2,B1>5)),TRUE, FALSE)

Have Fun!