I have two columns of names in my spreadsheet. In column 1 I have a long list of names, in column 2 I only have 10 names. I want to filter column 1 in such a way that it will only display the names that match those in column 2.

I've tried Queries, filters, arrays...etc With no luck. I just don't know how to filter/query one array of data, by another array of data.

I essentially want to filter column 1 by an array of names.

Picture of input (Column 1), what tho filter by (Column 2), and expected output:

enter image description here


I would filter by a regular expression such as

`^(Name 3|Name 6|Name 8|Name 33|Name 19)$`

which matches exactly the listed strings and nothing else. To build this from, say, column B, use

"^"&join("|", filter(B2:B, len(B2:B)))&"$"

where the filter part cuts out the blank cells in B column. Once the regular expression is built, just filter on regexmatch:

=filter(A2:A, regexmatch(A2:A, "^"&join("|", filter(B2:B, len(B2:B)))&"$"))
  • I'll give this a shot tomorrow. Is there any limit to using regex in Google docs? I expect the dataset I'll be filtering through to be in the hundreds of thousands of rows. I would imagine Google has imposed limits on processing time or something along those lines? – Douglas Gaskell Sep 25 '15 at 9:31
  • Yes, that is correct. It's unlikely that you will be able to compare each of 100,000,000 entries of column A against each of 100,000,000 entries of column B, no matter what approach you take. This is 10,000,000,000,000,000 string comparisons in total. Typically, one has to pay to get that much computational power. – user79865 Sep 25 '15 at 10:55
  • I get a feeling that this shouldn't have been closed as a duplicate, unless the only possible solution is regex. – Douglas Gaskell Sep 26 '15 at 19:33
  • @douglasg14b Please edit the question to include information that makes a regex solution undesirable. – user79865 Sep 26 '15 at 19:34

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