Help on "Yes's" and "No's" for matching data

[LisaAnn]

Hello:
I used the IFERROR formula to create yes's (when two columns of data have matches) and no's (when two columns of data don't have matches). My example appears to work for a lot of the data; however; I've found on some where they should have responded 'no' when they responded 'yes.' Similarly where they should have responded 'yes' when they responded 'no.'

Where am I going wrong here? See the spreadsheet. Thank you!

 

[AlanSidman]

Not sure about your formula. I don't really understand it. But, try this instead.

=IFNA(INDEX(G2:G73,MATCH(H2,G2:G73,0)),"NO"), and copy down. Will this do it for you.

 

[Peter Bartholomew]

I do not see any real evidence that your formulas are going wrong. The COUNTIFS appear to be correct when one looks at the numbers and MATCH is returning valid row numbers. The only comment I would make is that ISNUMBER offers a better way of testing for a positive match.
=IF(ISNUMBER(MATCH(value, list, 0),"Yes","No")

 

[GraH - Guido]

Hi [USER]LisaAnn[/USER],
This seems to be a continuation of your thread on vlookup. In such cases I would use a boolean true/false or 1/0 approach.
It would help us if you'd highlight where the result is wrong. I do see there is "YES" for matching empty cells.
[D2]=AND(A2<>"",COUNTIF(B$2:B$1500,A2))*1
[E2]=AND(A2<>"",COUNTIF(C$2:C$1500,A2))*1
[F2]=AND(B2<>"",COUNTIF(C$2:C$1500,B2))*1
[I2]=AND(G2<>"",COUNTIF(H$2:H$1500,G2))*1

 

[Peter Bartholomew]

This is just a trick building of Guido's recommendation:
= IF(value<>"", COUNTIFS(list,value), -1 )
The numbers are still there and can be used in further formulas but a custom number format
"Yes"; ;"No";@
will display positive numbers as "Yes", zero as "No" and negative numbers as blank.