Match multiple criteria

[Thomas Kuriakose]

Respect ed Sirs,

We have a worksheet with three columns of data to be matched with each other based on conditions and give result "okay" and "not okay and no data.

Kindly find attached the file for your kind reference.

1. If any cells in S1,S2, S3 is blank and two cells have the same value - Result - Okay, else not okay.
2. If the values in S1,S2,S3 are numbers then all cells should be same value, then okay, else not okay.
3. If there is no data in S1,S2,S3, then - Result - "No Data"

I tried OR and IF, but could not succeed.

I was reading on using COUNTIF for multiple criteria match, but could not understand.

Thank you very much for your support.

with regards,
thomas

 

[Khalid NGO]

Hi Thomas,
Good Day...

See if this works:

=IF(COUNTBLANK(B2:D2)>2,"No Data",IF(AND(OR(B2="",C2="",D2=""),OR(B2=C2,D2=C2,D2=B2)),"Okay",IF(OR(ISTEXT(B2),ISTEXT(C2),ISTEXT(D2)),"Okay","Not Okay")))

Regards,

 

[bosco_yip]

Or...........



In E2, formula copy down :

=IF(COUNTA(B2:D2),IF(((COUNT(B2:D2)=2)+(COUNTIF(B2:D2,"*")>0))*OR(B2=C2,C2=D2,B2=D2),"Okay","Not Okay"),"No Data")

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sirs,

Amazing and brilliant. Thank you both for providing this solution.

Much appreciated,

Thanks once again,

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sirs,

On small check. The condition if S1,S2 and S3 with numbers having the same value, should give okay, but it is giving not okay.

Kindly check the attached.

Thank you very much once again,

with regards,
thomas

 

[bosco_yip]

Respected Sirs,

On small check. The condition if S1,S2 and S3 with numbers having the same value, should give okay, but it is giving not okay.

Kindly check the attached.

Thank you very much once again,

with regards,
thomas

Make revised (red portion) in here :

=IF(COUNTA(B2:D2),IF(((AGGREGATE(14,6,COUNTIF(B2:D2,B2:D2),1)=3)+(COUNT(B2:D2)=2)+(COUNTIF(B2:D2,"*")>0))*OR(B2=C2,C2=D2,B2=D2),"Okay","Not Okay"),"No Data")

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Sir, now if we change the value in one cell B2 to a different value, we are not getting not okay, but it remains okay.

If the values in cells are numbers:

1. If all the values are the same we should get okay.
2. If one or more values change then it should be not okay


Kindly check the attached,

with regards,
thomas

 

[bosco_yip]

Respected Sir,

Sir, now if we change the value in one cell B2 to a different value, we are not getting not okay, but it remains okay.

If the values in cells are numbers:

1. If all the values are the same we should get okay.
2. If one or more values change then it should be not okay


Kindly check the attached,

with regards,
thomas

See revised file

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

This is perfect.

Thank you very much for the revised solution.

Much appreciated.

with regards,
thomas