CODEWITHSUNDEEP
excelgoogle-sheets
natiz
natiz

Reputation: 573

Google Spreadsheet: Compare 2 arrays

I'd like your assistance in coming up with a formula that would compare 2 arrays:

Array A:
444444
333333
222222

Array B
111111111
123124123
124124111
444444

I would like to compare the 2 arrays and return how many values exist in both arrays. So for these 2 arrays that appear on the top it should read 1 because only '444444' exists in both arrays.

The reason I'm speaking about arrays and not cells/columns/rows is because I use the =QUERY() function to get the arrays.

Upvotes: 2

Views: 8073

Answers (1)

AdamL
AdamL

Reputation: 24609

If all values in Array A are unique (or if you want to double-count duplicates etc):

=COUNTA(IFERROR(FILTER(Array_A;MATCH(Array_A;Array_B;0))))

If you needed to account for non-unique values in Array A, and not double-count them:

=COUNTA(IFERROR(FILTER(UNIQUE(Array_A);MATCH(UNIQUE(Array_A);Array_B;0))))

Upvotes: 8

Related Questions