Excel: Array formula returning unexpected result

Question

I have the formula:

=IF(C2=$C$1:C2,1,0)

which returns the value 1 (I believe this is correct as C2 will find a match with C2). My problem is that if I turn this into an array formula

{=IF(C2=$C$1:C2,1,0)}

this returns a value of 0, however I am not sure why.

Could someone provide an explanation on the different results from each formula?

what are you trying to achieve - to me neither of your formulae return any useful information... remember that {=IF(C2=$C$1:C2,1,0)} will return an ARRAY, so if other cells are filled in it is possible you are seeing only the first result from an array. — user3616725
– user3616725, Commented Oct 2, 2014 at 13:19

XOR LX · Accepted Answer · 2014-10-02 13:38:37Z

1

In the non-array version, the fact that you are passing an array of cells to the IF function is redundant, since, without array-coercion, the array resolves to just the first cell within that array, i.e. C1, so effectively the (non-array) construction:

=IF(C2=$C$1:C2,1,0)

is identical to simply:

=IF(C2=$C$1,1,0)

In the array version, a 1 will only be returned if the values in both C1 and C2 are equal to the value in C1 (though of course this is a little redundant in itself, since the value in C1 will always be equal to itself!).

What exactly are you trying to do with this odd-looking construction?

Regards

edited Oct 2, 2014 at 13:38

answered Oct 2, 2014 at 13:32

XOR LX

7,7821 gold badge19 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel: Array formula returning unexpected result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related