Compare 2 lists — Dynamic Array formula

Today, we want to com­pare two lists. Being more pre­cise, we want to find ele­ments that are on both lists, as well as ele­ments that are only on one list. 

This time, we will use Dynam­ic Array for­mu­las. It makes the oper­a­tion eas­i­er than using Lega­cy Excel formulas. 

We’re start­ing with the XMATCH func­tion. In the first two argu­ments, we write a list of ele­ments that we are look­ing for, and the list that those ele­ments are looked up in (Fig. 1)

=XMATCH(C2:C9,A2:A11)

In the results, we can see that we have #N/A errors in the places where Excel did­n’t find any match, and num­bers where it found the prop­er match (Fig. 2)

Now, we want to extract the val­ues where Excel has found the match. To to this, we can add the ISNUMBER func­tion to the XMATCH func­tion (Fig. 3)

=ISNUMBER(XMATCH(C2:C9,A2:A11))

Now, our results are FALSEs and TRUEs. What we can do now, is add the FILTER func­tion. In the first argu­ment, we have to write the array with ele­ments that we are look­ing for (Fig. 4)

=FILTER(C2:C9, ISNUMBER(XMATCH(C2:C9,A2:A11))

And we have our results. Now, we want to find ele­ments that are only on the My son’s list. All we need to do, is take the same for­mu­la and change the ISNUMBER func­tion into ISNA func­tion (Fig. 5)

=FILTER(C2:C9, ISNA(XMATCH(C2:C9,A2:A11))

And just like that we have found ele­ments that are on both lists and ele­ments that are only on one list (Fig. 6)

https://www.youtube.com/watch?v=I4QYLM7BpfQ