SOLVED

Perform a index match groupping data and with a condition

New Contributor

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

 

Have the following data:

 

DataOtro
ASI
ANo
ANo
ANo
Bsi
Bsi
Bsi
CNo
Csi
Dno
Dno

 

and having the following column 

FindResult
A 
B 
C 
D 

 

The final result should B

FindResult
ANo
BSi
CNo
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

4 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution

@PilarS325 

S1967.png

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.

@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"

@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"))

Great!!! thanks a lot. It works perfectly