How to compare 2 arrays with an unequal number of elements in Excel

Question

In the Excel array formula, I would like to test each element of one array with respect to each element of the second array when 2 arrays DO NOT have the same number of elements. Simplified right down, this scenario can be submitted:

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

NB - in my real-world scenario, these arrays are computed based on various previous steps.

Using the above example, I need the result: {TRUE, TRUE, FALSE, FALSE, FALSE}. I get {TRUE, TRUE, # N / A, # N / A, # N / A}.

It is clear that when testing more than 1 value, Excel wants an equal number of elements in 2 arrays; when not, error # N / A fills in the blanks.

I thought about writing UDF to achieve what I want, and I'm sure my coding skills come down to creating something like:

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

But I would rather do it using the built-in functionality if it is not too bulky ...

So, a simple question; can build an array formula to accomplish what i'm after?

Thanks for attention!

Answer 1

If the second array is a subset of the first array of the same order and starting at position 1, you can use this array formula to test equivalence:

=IFERROR(IF({1,2,3,4,5}={1,2},TRUE),FALSE) 

For nonequivalence, simply replace FALSE and TRUE

 =IFERROR(IF({1,2,3,4,5}={1,2},FALSE),TRUE) 

Then you can use this in other formulas just like an array:

However, if the arrays are out of order, as in this example:

 {1,2,3,4,5},{1,4,5} 

Then you should use MATCH . However, all you need to do is surround the match with ISNUMBER as follows:

Equivalence:

 =ISNUMBER(MATCH({1,2,3,4,5},{1,4,5},0)) 

Inequivalence:

 =NOT(ISNUMBER(MATCH({1,2,3,4,5},{1,4,5},0))) 

Remember that all array formulas are entered using ctrl + shift + enter

Answer 2

Using the MATCH function is probably the best way ..... but if you really want to compare each element in one array with another array in direct comparison, you need to be a “column” and a “row”, for example

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

Note the double colon separator in the second array

If you cannot actually change the designation of the column / row, then you can use TRANSPOSE , i.e.

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

You cannot get the required results if the arrays contain duplicates, because then you get some double count, for example. 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

Answer 3

May be:

 {=IF(ISERROR(MATCH({1,2,3,4,5},{1,2},0)),FALSE,TRUE)}