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