Return Array where value in another Array.

[ko1967]

I have an array (lets call it Array1) and another array (lets call it Array2) as follows.

Array1 = {A,B,C,D}
Array2 = {A,C}

I'm trying to return an array (same length as Array1) where the value will be true if the item in Array1 is found in Array2.

Essentially from the above I want to return {TRUE, FALSE, TRUE, FALSE} because A and C are in Array2 and B and D are not. Every solution I've tried returns an array of 2 elements not 4. I'm on office 365 so I have access to all the new dynamic array functions.

Thanks.

 

[Rick Rothstein]

Are these "arrays" one dimensional VBA arrays or cell range arrays?

Can you show us how the arrays are populated?

Is Array2 always going to contain less elements than Array1?

 

[ko1967]

The arrays are contained in cell ranges and yes, Array2 always has less elements than Array1. I'm looking for a formula solution, not a VBA solution.

 

[Fluff]

Is this what you mean?

+Fluff New.xlsm
	J	K	L
2	A	A	TRUE
3	B	C	FALSE
4	C		TRUE
5	D		FALSE
Master

Cell Formulas
Range	Formula
L2:L5	L2	=ISNUMBER(MATCH(J2:J5,K2:K3,0))
Dynamic array formulas.

 

[Rick Rothstein]

Is this what you mean?

+Fluff New.xlsm
	J	K	L
2	A	A	TRUE
3	B	C	FALSE
4	C		TRUE
5	D		FALSE
Master

Cell Formulas
Range	Formula
L2:L5	L2	=ISNUMBER(MATCH(J2:J5,K2:K3,0))
Dynamic array formulas.

If you are correct in setup and the type of formula (multi-cell selected, array entered) the OP wants, then this should also work...

=COUNTIF(K2:K3,J2:J5)>0

 

[ko1967]

Thanks @Fluff. I was all over match and index and filter and must couldn't come up with what you provided.

Thanks @Rick Rothstein as your solution also works.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ko1967]

Not done quite yet, if you don't mind. What I provided was a very simple example of a more complex problem.

Let's assume the following.

=COUNTIFS({1,3},{1,2,3,4}) returns {1,0,1,0}
=COUNTIFS(K2:K3,J2:J5) also returns {1,0,1,0}

So COUNTIFS evalutes the 2 ranges into 2 arrays which we all know.

So lets set X1 = FILTER(K2:K4,P2:P4=1) such that the result is a spilled array returning {1,3} which is the same as the values in the array K2:K3.

Now lets set Y1 =COUNTIFS(X1#,J2:J5) and it returns {1,0,1,0} as expected.

However, set Z1 =COUNTIFS(FILTER(K2:K4,P2:P4=1),J2:J5) and Excel will issue an error (There is a problem with the formula) when you press enter. You can use F9 to evaluate both the first and second argument of the COUNTIFS function and it still will issue the same error when you press enter.

@Rick Rothstein ... any clue what the issue is here before I report it to Microsoft as a bug?

@Fluff ... good news is =ISNUMBER(MATCH(J2:J5,FILTER(K2:K4,P2:P4=1),0)) works as expected.

Fun w/ dynamic arrays

 

[Fluff]

Countifs needs a range, not an array.
Also this
=COUNTIFS({1,3},{1,2,3,4})
does not work for me, for the same reason.

 

[ko1967]

Thanks again @Fluff ... it's the little things that get ya