MATCH/INDEX: Confirm a "Specific" Text, if any returns differ mark final return value as "No", if all match mark as "Yes"

[Morey]

Hello all,

I would like to INDEX/MATCH while confirming a "Specific" text. If any returns differ from "Specific" text, mark final return value as "No". If all match mark as "Yes". Any help or suggestions would be greatly appreciated.


Modifiable Formula:

=IFERROR(INDEX('Data (O&S+PC&I)'!$AI$2:$AI$5000,MATCH($B$2,'Data (O&S+PC&I)'!$A$2:$A$5000,0))&"", "")

Regards,

Morey

 

[Marcelo Branco]

Could you provide a small data sample along with expected result(s)?

M.

 

[JefferyLi]

I dont know what your data is but based on the description you should use a if formula instead of iferror.

if(INDEX('Data (O&S+PC&I)'!$AI$2:$AI$5000,MATCH($B$2,'Data (O&S+PC&I)'!$A$2:$A$5000,0))<>"Specific","No","Yes")

 

[Morey]

Jeff,

I dont know what your data is but based on the description you should use a if formula instead of iferror.

if(INDEX('Data (O&S+PC&I)'!$AI$2:$AI$5000,MATCH($B$2,'Data (O&S+PC&I)'!$A$2:$A$5000,0))<>"Specific","No","Yes")

This does work. What if two matching values are found but one, does not match "specific" text? In this case, return would be No. How would I modify formula to suit?

=IF(INDEX('PRPO CLN'!L2:L5000,MATCH(A1,'PRPO CLN'!E2:E5000,0))<>"Fully Released","No","Yes")

Regards,

Morey

 

[Marcelo Branco]

Maybe...

=IF(COUNTIFS('PRPO CLN'!$E$2:$E$5000,A1,'PRPO CLN'!L2:L5000,"Fully Released"),"Yes","No")

M.

 

[JefferyLi]

um.. I dont know if I understand correctly.

try this (if index result is not "something1", or index result is not "Something2", then mark as "NO", rest of them mark as "Yes"

=IF(OR(INDEX('Data (O&S+PC&I)'!$AI$2:$AI$5000,MATCH($B$2,'Data (O&S+PC&I)'!$A$2:$A$5000,0))<>"Specific",INDEX('Data (O&S+PC&I)'!$AI$2:$AI$5000,MATCH($B$2,'Data (O&S+PC&I)'!$A$2:$A$5000,0))<>"Specific"),"No","Yes")