Issue

This Content is from Stack Overflow. Question asked by IMTheNachoMan

I have an example sheet at: https://docs.google.com/spreadsheets/d/1VucCza1F1xi9s61MXBuOu_122yPyGjQdundYHipXnS4/edit?usp=sharing.

I have two sheets:

Sheet1 has a table with two columns:
- column 1 has a unique string
- column 2 has a new line separated strings
Sheet2 has a table with two columns:
- column 1 has a string
- column 2 has a string but no new lines

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.

Solution

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.

This Question was asked in StackOverflow by IMTheNachoMan and Answered by Aaron Dunigan AtLee It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.