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 ARRAYFORMULA inside MAP.
Some example data to work on: On sheet 1 I have several names.
| A | B | C |
|---|---|---|
| alice | ||
| robert | bob | smith |
| callum | cal | |
| daisy |
On sheet 2 I have a bunch of data, some cells of which refer to the names on sheet 1.
| A | B | C | D |
|---|---|---|---|
| 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:
=MAP(A1:D5,LAMBDA(x,ARRAYFORMULA(IF(REGEXMATCH(x,Sheet1!A1:C4),Sheet1!A1:C4,0)))))
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
