Conditional Formatting: multiple conditions with indirect references-CodePudding

I want to have text on a cell of one sheet highlighted if and only if there is a same string on another sheet AND the checkbox next to it is marked true:

Sheet 1 (the current sheet that I'm doing conditional formatting on) contains "UNIQUETEXT751" on B column

Sheet 2 (named "Completed") contains "UNIQUETEXT751" on C column AND the checkbox on A column is marked true

It should highlight the cell on Sheet 1 with "UNIQUETEXT751"

These are the following things I've tried...

=match(B2:B,INDIRECT("Completed!C2:C"),0)*(INDIRECT("Completed!A2:A")=true)

=match(B2:B,INDIRECT("Completed!C2:C"),0)*(COUNTIF(INDIRECT("Completed!$A:$A")),true)

=AND(match(B2:B,INDIRECT("Completed!C2:C"),0),(COUNTIF(INDIRECT("Completed!$A:$A"),$A2)))

None of them seems to work.

EDIT: Here's a test sheet with examples.

CodePudding user response：

try:

=INDEX(REGEXMATCH(B1, TEXTJOIN("|", 1, 
 FILTER(INDIRECT("Completed!C:C"), INDIRECT("Completed!A:A")=TRUE))))

CodePudding user response：

try:

=MATCH(B1, FILTER(INDIRECT("Completed!C:C"), INDIRECT("Completed!A:A")=TRUE), 0)