How To Determine Match from Multiple Cells in One Row-CodePudding

Editable Test Sheet:

Although I believe what I'm trying to do is fairly simple, It is difficult for me to even find the correct words to describe what I'm trying to do. I suspect that is why I've been researching all day for an answer and cannot find it.

I need an ARRAYFORMULA that can check for the existence of a given "PARENT ID" in another table, but only if another cell in the same row is not true.

A given PARENT ID from TABLE 2 could appear in TABLE 1 multiple times, sometimes paired with "TRUE" and sometimes not. I only need to know if the PARENT ID from TABLE 1 appears in TABLE 2 along with the value TRUE in the "Done" column next to it. If it appears even once, I want to denote it in TABLE 2.

I am able to do this in various forms, but none of them work with ARRAYFORMULA.

See the shared example sheet above. I would greatly appreciate any help.

CodePudding user response：

See my two newly added sheets ("Erik Help" and "Erik Help 2"). Below are the formulas I used:

In "Erik Help":

=ArrayFormula({"In Table 1 and Not Done?";IF(E3:E="",,IF(ISERROR(VLOOKUP(E3:E,TRIM(B3:B&C3:C),1,FALSE)),,TRUE))})

This delivers the results you want as asked (though your manually entered results did not list F3 as TRUE when I believe it should be).

The trick here is looking for the Parent ID within a virtual column that concatenates Col B and Col C and then TRIMs out extra characters (such as null). If there is an exact match, we know that there was no value in Col B. And since the only valid Col-B value is Boolean TRUE, we are assured that any match is in effect false (or not Done).

In "Erik Help 2":

=ArrayFormula({"In Table 1 and Not Done?";IF(E3:E="",,IF(ISERROR(VLOOKUP(E3:E,TRIM(B3:B&C3:C),1,FALSE)),,IFERROR("Item ID(s): "&VLOOKUP(E3:E,REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({FILTER(C3:C,A3:A<>"",B3:B<>TRUE)&"~",FILTER(A3:A&" (R"&ROW(A3:A)&")",A3:A<>"",B3:B<>TRUE)&","}, "Select MAX(Col2) GROUP BY Col2 PIVOT Col1"),, 9^9)),"~")),"[,\s] $",""),2,FALSE),TRUE)))})

Instead of simply TRUE, this version returns the Item ID(s) that are not done for that Parent ID along with the row number where each incomplete Item ID is found.

Since you didn't expressly ask for this, I'm considering it bonus material and will not take time to explain it.

CodePudding user response：

Another answer I received on another forum:

=ArrayFormula(IF(COUNTIF(C3:C&B3:B,E3:E),true,))

Credit to Prashanth KV