Matching Criteria across Multiple Columns

[XJ8000]

Please help, I'm trying to work an excel formula in order to report on a table of data.

I’d like to return a "Yes" when unique entries in column A, when the corresponding range of entries in column B are unique. The data set is:

Table1

Column A	Column B
A	20
A	20
B	30
B	2
C	40
C	40
C	40
D	30

<tbody>
</tbody>

The formula in Table 2 in Column B2 is

'=IF(AND(EXACT(INDEX(Table1!A:B,MATCH(A2,Table1!A:A,0),2),Table1!B:B))=TRUE,"YES","No")'

and the formula in B3 is

'=IF(AND(EXACT(INDEX(Table1!A:B,MATCH(A3,Table1!A:A,0),2),Table1!B:B))=TRUE,"YES","No")' etc etc.

The output in Column 2 is as follows:

Table2

Column A	Column B
A	YES
B	YES
C	YES
D	No

<tbody>
</tbody>

When in fact the output should read:

Column A	Column B
A	YES
B	No
C	YES
D	YES

<tbody>
</tbody>

Many Thanks

 

[Canapone]

Hi

a possible approach in B3, array entered (control+shift+enter)


=IF(SUM(IF(FREQUENCY(IF('Table 1'!A$2:A$100=A3,MATCH("~"&'Table 1'!B$2:B$100,'Table 1'!B$2:B$100&"",0)),ROW($3:$100)-ROW($A$2)+1),1))-1;"No","Yes")

Regards

 

[XJ8000]

Thank you - that is great - had to make a slight mod to formula but otherwise perfect and much appreaciated - in B3 the formula is:

=IF(SUM(IF(FREQUENCY(IF(Table1!A$2:A$101=A3,MATCH("~"&Table1!B$2:B$101,Table1!B$2:B$101&"",0)),ROW(Table1!$2:$101)-ROW($A3)+1),1))-1,"No","Yes")

 

[jtakw]

Hi,

Here's a Non-Array entered formula option, change/adjust cell references/range and add sheet names as needed:

Book1
	A	B	C	D	E
1	A	20		A	YES
2	A	20		B	NO
3	B	30		C	YES
4	B	2		D	YES
5	C	40
6	C	40
7	C	40
8	D	30
Sheet389

Cell Formulas
Range	Formula
E1	=IF(COUNTIF(A$1:A$8,D1)=COUNTIFS(A$1:A$8,D1,B$1:B$8,VLOOKUP(D1,A$1:B$8,2,0)),"YES","NO")

 

[mikerickson]

Perhaps
=IF(COUNTIFS(A:A,D1,B:B,"<>"&VLOOKUP(D1,A:B,2,FALSE))=0,"Yes","No")