1

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

0

1 Answer 1

0

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)

enter image description here

2
  • This works great, though I don't fully understand how it works. For any future reference, I threw that entire thing within another array search formula to narrow it down to a single T/F value by searching for TRUE within the result (I changed the results YES/xxxxx to TRUE/FALSE). This seems to take care of things, thanks! =arrayformula(iferror(if(search(TRUE, Jan 7, 2020 at 19:45
  • Glad it worked for you. :) Jan 7, 2020 at 19:52

Your Answer

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.