Perform a index match groupping data and with a condition

[PilarS325]

I am going around in circles and I am looking for help.  the situation is:

Have the following data:

Data	Otro
A	SI
A	No
A	No
A	No
B	si
B	si
B	si
C	No
C	si
D	no
D	no

and having the following column 

Find	Result
A
B
C
D

The final result should B

Find	Result
A	No
B	Si
C	No
D	No

The idea is looking for the values in the column "Find", lets get the first values as example "A".  Look for A in the column Data, and if all the values in column "Otro" are "si", then the result will be "Si", if any of the values for A is no, then the result will be know

How can I do it?  

thansk a lot for your help

[#2 · Accepted solution]

@PilarS325 

The formula in E2 is

=IF(COUNTIFS($A$2:$A$12,D2,$B$2:$B$12,"no")=0,"Si","No")

You'll have to adjust the ranges for your layout.

[#3]

@Hans Vogelaar  It works great but I have another question, how could I adapt the formula in the event that I have data in column D that it is not in column A?    For those cases I will like to return the value "Not in list"

[#4]

@PilarS325 

=IF(COUNTIF($A$2:$A$12,D2)=0,"Not in list",IF(COUNTIFS($A$2:$A$12,D2,$B$2:$B$12,"no")=0,"Si","No"))

[#5]

Great!!! thanks a lot. It works perfectly