Identify from Match and Return

[rajamdade]

Hello,

I am having datasheet as below
Accordingly, for values in Column A:
1) Correct=1
2) Correct and/or incorrect and/or ambigious= 0

I want to match the species from column C to the Column B and look in the column A whether they possess correct/ambigious/incorrect
Now, in column D 'Result' = Return match with only 'correct' identification to print 1, but if the species column possess any other identification as correct/ambigious/incorrect then print 0

For example, "Aerva javanica" is having all correct so = 1...
But, "Aegilops triuncialis" is having correct, ambigious as well as incorrect so = 0

In short I need to find the species that are having only correct value all over, if any other value instead of correct is observed then return 0

Please suggest formula/function accordingly

(col. A) Identification	(col. B) Species	(col. C) Match	(col. D) Result
ambiguous	Aaronsohnia factorovskyi	Aaronsohnia factorovskyi	0
ambiguous	Aaronsohnia factorovskyi	Aegilops triuncialis	0
correct	Aegilops triuncialis	Aerva javanica	1
ambigious	Aegilops triuncialis	Aeluropus lagopoides	0
incorrect	Aegilops triuncialis
incorrect	Aeluropus lagopoides
incorrect	Aeluropus lagopoides
correct	Aerva javanica
correct	Aerva javanica
correct	Aerva javanica

Help appreciated, thanks in advance..

[Pete_UK]

Try this in D2:

=IF(A2="correct",1,0)

then copy down. An alternative (slightly shorter) version is:

=--(A2="correct")

Hope this helps.

Pete

[rajamdade]

But "Aegilops triuncialis" is having correct, ambigious as well as incorrect then the result needs to be 0...
In short I need to find the species that are having only correct value all over, if any other value instead of correct is observed then return 0

[AliGW]

That is precisely what this does:

=IF(A2="correct",1,0)

[rajamdade]

Thanks for your reply,
Might be my mistake to make understand what I need...

I want to match the species from column C to the Column B and look in the column A whether they possess correct/ambigious/incorrect

in column D 'Result' = Return match with only 'correct' identification to print 1, but if the species column possess any other identification as correct/ambigious/incorrect then print 0

Thanks

[Pete_UK]

Your layout was not very clear. Put this in D2:

=IF(COUNTIF(B:B,C2)=COUNTIFS(B:B,C2,A:A,"correct"),1,0)

then copy down.

Hope this helps.

Pete

[rajamdade]

Yes! it solved....
Thank you very much Pete_UK and AliGW

[Pete_UK]

You're welcome - thanks for the rep.

Pete