Forum Discussion

OleksandrYu's avatar
OleksandrYu
Copper Contributor
Oct 23, 2023
Solved

Comparison of arrays with different dimensions

So, I wanted to fully understand how Excel compares arrays and encountered quite strange behaviour. Here are the cases I tested:
1) Comparison of arrays of any size and single cell - works pretty clearly, it compares each cell of the array with a single cell. 
2) Comparison of same-size arrays - compares each cell with the corresponding in the second array. 
3) Comparison of a single column or single row array with other arrays - not sure about that one, it looks like Excel is replicating those rows or columns in a direction that is dependent on a single column/row array, here are examples:


However, in 3thd case, it's not obvious what way will Excell replicate the 1st Array. I mean here it replicated it vertically, why not horizontally? I thought maybe it is dependent on the way Excel compares them in formula evaluation, here is an example:


But let's not jump to conclusions, and investigate other cases.
4) Comparison of not single row/column arrays with other arrays - I thought that Excel would replicate them the same way it does with single row/column arrays, but it does not, here is an example:


it could replicate them horizontally or vertically, but it doesn't, why so?
The same goes for those cases:


5) Comparison of single row/column array with other single row/column array - here is a completely new behaviour, when Excell is duplicating rows or columns to make a complete array and compares them as same size arrays, here is an example:

Okay, then I thought the same thing would work for not single row/column arrays where Excel would replicate them to make them the same size, but no 🙂
6) Comparison of not single row/column array with other not single row/column arrays that are of different sizes, so that they cannot be laid over one another to cover all cells of an array. 
Here Excel will not replicate those arrays to make them of the same size but will actually compare only those cells that are the result of the intersection of 2 arrays. Why it does not behave the same way it does with single row/column arrays?

Sorry for possible unclarities, feel free to ask any questions. Any help or info on how Excel is comparing those arrays will be much appreciated! My goal is to summarise all cases to understand the way Excell behaves.
Cheers!

  • OleksandrYu 

    Resulting array always have maximum number of rows/columns of source arrays. If we compare 1D array, i.e. single row or column or in particular single cell, it iterates through rows/columns of 2D array. If both source arrays are 2D it compares elements from left to right and from top to bottom returning #N/A when we out of size of the one of arrays.

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    OleksandrYu 

    Resulting array always have maximum number of rows/columns of source arrays. If we compare 1D array, i.e. single row or column or in particular single cell, it iterates through rows/columns of 2D array. If both source arrays are 2D it compares elements from left to right and from top to bottom returning #N/A when we out of size of the one of arrays.

Resources