SOLVED

# Perform a index match groupping data and with a condition

Copper Contributor

# Perform a index match groupping data and with a condition

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

4 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution

# Re: Perform a index match groupping data and with a condition

The formula in E2 is

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

# Re: Perform a index match groupping data and with a condition

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

# Re: Perform a index match groupping data and with a condition

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

# Re: Perform a index match groupping data and with a condition

Great!!! thanks a lot. It works perfectly
1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

# Re: Perform a index match groupping data and with a condition

The formula in E2 is

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