CODEWITHSUNDEEP
excel
OBT
OBT

Reputation: 59

EXCEL - Array formula match in list

I am trying to make a formula that returns into a single cell (array formula) a vector of True/False based on whether each element in an array matches (identically) or not any of the elements in another array.

Example:

Array to be matched (array_compare): [A;A;B;C;D]

Array with elements (array_elements): [A;B;D]

The formula should return something like:

={formula(array_compare;array_elements)} ==> [TRUE;TRUE:TRUE;FALSE;TRUE]

I need this mid-step function so later on I can add rows or columns based on criteria or tell how many matching item there are in array_compare.

For example (for later use):

=sum(--formula(array_compare;array_elements))} ==> 4 (in the example)

THANKS!

Upvotes: 0

Views: 1047

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

You can use ISNUMBER(MATCH())

=SUMPRODUCT(--ISNUMBER(MATCH({"A","A","B","C","D"},{"A","B","D"},0)))

This will return 4 as it will iterate the large array.

If you want to iterate the smaller array reverse the two arrays and it will return 3:

=SUMPRODUCT(--ISNUMBER(MATCH({"A","B","D"},{"A","A","B","C","D"},0)))

Upvotes: 5

Related Questions