Need Help - Lookup Specific Value In a List

[ranjith2523]

Hi Friends,

I need your help to lookup and find out a specific value from a list. I am using office 365 and below provided a sample data of my requirement.

Category	Status		Category	Result for New
Data	Old		Data	Yes
Data	New		Style	No
Data	Existing
Style	Existing
Style	Old
Style	Rejected

I need to check whether "New" status exists for the specific Category. I need to apply the formula in cell E2 and below. In my example the cell E2 reflects as "Yes" because the status "New" found in column B against the category "Data". The E3 reflects as "No" because the status "New" not found in column B against "Style" category.

Obviously, vlookup will find the 1st match value and even i tried with Index and match but not able to achieve the expected result. Can someone please help me on this.

Regards
Ranjith

 

[Fluff]

How about

=IF(COUNTIFS(A:A,D2,B:B,"New"),"Yes","No")

 

[ranjith2523]

How about

=IF(COUNTIFS(A:A,D2,B:B,"New"),"Yes","No")

Oh Yes, Thank you Mr.Fluff.. this is what i expected

 

[Fluff]

You're welcome & thanks for the feedback.