1

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

1 Answer 1

1

Most likely, your difficulties are connected with the fact that "Conditional formatting Rules only work on the current Sheet. This is by design. So when you point to another Sheet it will not work."

Just use INDIRECT() to refer to data from another sheet and the formula like as

=REGEXMATCH(A1,"\b(?:"&TEXTJOIN("|",1,INDIRECT("Sheet1!$A$1:$C$4"))&")\b")

will colorize the cells the way you want.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.