Essentially, I would like to iterate over two 2-dimensional arrays of cells, and compare each pair of cells via REGEXMATCH.
I briefly attempted nesting
ARRAYFORMULA, however this didn't seem to give the required double iteration at all. My latest attempt involves trying to nest
Some example data to work on: On sheet 1 I have several names.
On sheet 2 I have a bunch of data, some cells of which refer to the names on sheet 1.
|alice went to the shops||derek bought bacon|
|at home is georgia||felix is cooking||eggs callum buys||bacon and eggs|
|bob isn't at georgia's|
|derek again||no names here|
|felix and derek||something again||out went alice|
The goal is for each cell in sheet 2, to see if it contains any name from sheet 1.
My latest attempt looks like this:
However this throws an error: "Single value expected. Nested array results are not supported." I tried reversing the nesting inclusion, but that appears to be even worse with complaints about different sized arrays, as well as not fixing the original error.
For context, I would ultimately replace the output with just 1 or 0, and use the formula in conditional formatting to colour the cells in sheet 2 if they contain a name from sheet 1.
Here is an example spreadsheet with data as above, together with an additional copy of sheet 2 with the corresponding cells coloured. Example Spreadsheet