How to compare 2 arrays with unequal numbers of elements in Excel

Question

In an Excel array formula, I would like to test each element of one array against each element of a second array, when the 2 arrays do NOT have the same number of elements. Simplified right down, this scenario could be represented by:

=SUMPRODUCT({1,2,3,4,5}={1,2})

NB - in my real world scenario these arrays are calculated from various prior steps.

Using the above example, I would want a result of {TRUE,TRUE,FALSE,FALSE,FALSE}. What I get is {TRUE,TRUE,#N/A,#N/A,#N/A}.

It's clear that, when there's more than 1 value being tested for, Excel wants equal numbers of elements in the 2 arrays; when there isn't, the #N/A error fills in the blanks.

I've considered writing a UDF to achieve what I want, and I'm pretty sure my coding skills are up to creating something like:

=ArrayCompare({1,2,3,4,5},"=",{1,2})

But I'd much rather do this using native functionality if it's not too cumbersome...

So, simple question; can an array formula be constructed to do what I'm after?

Thanks peeps!

Answer 1

Using MATCH function is probably the best way.....but if you actually want to compare every element in one array with another array in a direct comparison then one should be a "column" and one a "row", e.g.

=SUMPRODUCT(({1,2,3,4,5}={1;4})+0)

Note the semi-colon separator in the second array

If you can't actually change the column/row designation then TRANSPOSE can be used, i.e.

=SUMPRODUCT(({1,2,3,4,5}=TRANSPOSE({1,4}))+0)

You may not get the required results if the arrays contain duplicates because then you will get some double-counting, e.g. with this formula

=SUMPRODUCT(({1,1,1,1,1}={1;1})+0)

the result is 10 because there are 5x2 comparisons and they are all TRUE