# Issue

This Content is from Stack Overflow. Question asked by IMTheNachoMan

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.```