Array lookup

Closed
Murtuza Posts 6 Registration date Wednesday 4 September 2013 Status Member Last seen 7 September 2013 - 4 Sep 2013 à 13:43
 Blocked Profile - 4 Sep 2013 à 14:18
Hello,
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 B
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 thrice.it 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 true.so 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
4 Sep 2013 à 14:18
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!