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

Morey

New Member
Joined
Sep 24, 2018
Messages
37
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:

Code:
=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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

JefferyLi

New Member
Joined
Jul 16, 2019
Messages
9
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

New Member
Joined
Sep 24, 2018
Messages
37
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?

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

Morey
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Maybe...

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

M.
 

JefferyLi

New Member
Joined
Jul 16, 2019
Messages
9
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,102,454
Messages
5,486,996
Members
407,575
Latest member
calc

This Week's Hot Topics

Top