Two Criteria Are Met

[Jak7217]

Hey All:

This should be simple, but I can't think of the best way to write this formula. I have a huge access listing with e-mail addresses in one column and access rights in another column. There are duplicate e-mail addresses if the individual has multiple access rights. I need to write a lookup where I define the e-mail address and the access right and the formula will identify if both are found in the listing.

Thanks in advance!

Justin

 

[etaf]

you could use conditional formatting and colour those -
how do you want to

the formula will identify if both are found in the listing.

BOTH, does that mean there are only 2 access rights

assuming email in A and Access rights in B
=countifs(A2:A1000, A2, B2:B1000,B2)
will give a number

if you add conditional formatting
=countifs(A2:A1000, A2, B2:B1000,B2)>1

that will show any duplicates

in conditional formatting - it will colour

thats one way

Various.xlsx
	A	B
1	Email	Access
2	1@2.com	A
3	2@2.com	B
4	3@2.com	A
5	2@2.com	B
6	4@2.com	A
7	2@2.com	B
8	1@2.com	A
9	2@2.com	B
10	3@3.com	A
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B10	Expression	=COUNTIFS($A$2:$A$10,$A2, $B$2:$B$10,$B2)>1	text	NO

OR

Various.xlsx
	A	B	C
1	Email	Access	Count
2	1@2.com	A	2
3	2@2.com	B	4
4	3@2.com	A	1
5	2@2.com	B	4
6	4@2.com	A	1
7	2@2.com	B	4
8	1@2.com	A	2
9	2@2.com	B	4
10	3@3.com	A	1
Sheet3

Cell Formulas
Range	Formula
C2:C10	C2	=COUNTIFS($A$2:$A$10,$A2, $B$2:$B$10,$B2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B10	Expression	=COUNTIFS($A$2:$A$10,$A2, $B$2:$B$10,$B2)>1	text	NO