Google Sheets - Identify if one of many matches any in an array

Question

I am trying to determine if an array of values in Google Sheets has any matches within another array of values. The data set is constantly expanding, so I want to use an Arrayformula to handle it.

---

This is the closest I've gotten so far:

=ARRAYFORMULA( IF( IFERROR( VLOOKUP( SPLIT($K2:K, ",", TRUE, TRUE ), FILTER( Tags!$A$2:$A, Tags!$A$2:$A<>"", Tags!$C$2:$C=1 ), 1, FALSE ), FALSE )<>FALSE, TRUE ))

The data is comma separated in a single cell, so I start by using the split function to separate each value. I then vlookup the value in a filtered table of values that are being categorized by type.

=VLOOKUP( SPLIT( $K2:K, ",", TRUE, TRUE ), FILTER( Tags!$A$2:$A, Tags!$A$2:$A<>"", Tags!$C$2:$C=1 )

I then use IFERROR to mark any items not found as FALSE, and an IF function to mark the rest as TRUE.

=IF( IFERROR( VLOOKUP(above), FALSE )<>FALSE, TRUE ))

By wrapping it all in an arrayformula, I've been able to produce an array of T/F values based on the IDs in the cell.

---

I want the result to be a single column that tells me if any are true.

I would try to use join or sum of these as numbers, but those don't work with an arrayformula and the data will constantly be expanding so it would require constantly adjusting the cells to more columns.

I feel like using Query may allow me more flexibility here, but I can't seem to wrap my mind around using it very well so I haven't gotten far on that.

Here's a spreadsheet containing the data I'm working with (irrelevant data is hidden/removed): https://docs.google.com/spreadsheets/d/1DcTq2hsX7tNfw2f31oPzVqZu8V7JVKNAJUdqQMcinFI/edit#gid=1916289043

Answer 1

If I understood your question correctly the following formula should produce your desired results:

=ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(FILTER(arrayformula(if(iferror(
    VLOOKUP(SPLIT($K3:K, ",", TRUE, TRUE), 
       QUERY(Tags!A2:C28,"SELECT A,'z' WHERE C=1 label 'z' ''"), 1, 0),
           FALSE)<>FALSE, "YES","xxxxx")),LEN(A3:A)>1)),,999^99))))

(I changed the resulting data to xxxxx and YES just so as to make it easier to read. You can change them to whatever you want)