Check if all new line separated strings exist in another sheet using a lookup column-CodePudding

I have an example sheet at:

Sheet2

I want to put an ARRAYFORMULA in Sheet1.column 3 that will tell me if all of the values in Sheet1.column 2 for Sheet1.column 1 have a matching row in Sheet2.

In my example sheet I have a column showing expected output and explanation.

Also, I am on Google Workspace Enterprise and won't have the new functions (map, lambda, etc...) for a while. So I need something that works without them.

CodePudding user response：

I'm not certain whether you're looking for a single array formula to place in cell C1 of Sheet1 (it sounds like that, but I'm not sure such a thing is possible). It is possible to write a formula that can be copied down column C, that will generate the expected output in column F. Here's the formula for C2:

=PRODUCT(ARRAYFORMULA(IFERROR(MATCH(SPLIT(B2,CHAR(10)),FILTER(Sheet2!B:B,Sheet2!A:A=A2),0),0)))>0

Breakdown

FILTER(Sheet2!B:B,Sheet2!A:A=A2) creates an array of all the values in column B of Sheet2 that have a corresponding value of e.g. "alpha" in column A (using the values in your example sheet).

SPLIT(B2,CHAR(10)) creates an array of the individual newline-separated strings in cell B2.

MATCH(..., ..., 0) searches the second array for each value in the first array, and returns an array of indices showing where they are found (or an error if not found)

IFERROR(..., 0) converts the errors in that array to 0's.

ARRAYFORMULA(...) ensures that the above returns an array (if not present it only returns the first value in the array).

At this point we have an array whose values are 0 if the desired value was not found, non-zero if the value was found. We want to know whether ALL those values are non-zero.

PRODUCT(...) multiplies all those values together, and the result will be 0 if and only if some value was not found. Hence the >0 which returns TRUE if the result is non-zero and FALSE if 0.

Assumptions

I'm assuming here that the search is only one-way. i.e. If the values in column B of Sheet1 are a subset of the values in Sheet2, my formula will still return TRUE and will not notice if there are extraneous values in Sheet2.

CodePudding user response：

Use this formula

=IF(A2="",,IFNA(
           IF(ISTEXT(FILTER(
                     CONCAT(Sheet2!$A$2:$A,Sheet2!$B$2:$B),
                     CONCAT(Sheet2!$A$2:$A,Sheet2!$B$2:$B)=
                     CONCAT(A2,B2))),TRUE,FALSE),FALSE))

CodePudding user response：

=ARRAYFORMULA(
  {"Output";
    MAP(A2:A7,B2:B7,
    LAMBDA(a,b,
      BYROW(
        a=XLOOKUP(
          SPLIT(b,CHAR(10)),Sheet2!B:B,Sheet2!A:A
        ),
        LAMBDA(x,IFNA(AND(x),FALSE))
      )
    )
    )
  }
)

SPLIT column b by new lines CHAR(10)
LOOKUP each of those words in Sheet2 and see if the result is equal to column a
Aggregate the boolean results for each ROW with AND
Repeat the process for all the cells in range A2:B7 using MAP