Google Spreadsheet: Compare 2 arrays
excel, google-sheets
Solution
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))))`
Problem
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.