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

New contributor
Alex is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

1 Answer 1

0

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

Alex is a new contributor. Be nice, and check out our Code of Conduct.

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

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