Compare 2 lists — Legacy Excel classic formulas

Today, we want to com­pare two lists and find ele­ments that are on both lists as well as on only one. 

We are going to use Clas­sic Excel, Lega­cy Excel and a clas­sic for­mu­la with the AGGREGATE func­tion. It means that this for­mu­la will work from 2010 and thanks to this, we won’t need to use the Crtl + Shift + Enter key combination.

Let’s start with find­ing ele­ments. We will be check­ing whether ele­ments from My son’s favorite movies list are on My daugh­ter’s favorite movies list. We can use the MATCH func­tion here. We are look­ing up ele­ments from my son’s list (list B) on my daugh­ter’s list (list A). We’re click­ing F4 key to lock both lists. Then we write zero to have the exact match and that’s it (Fig. 1)

=MATCH($C$2:$C$9,$A$2:$A$11,0)

Excel has just giv­en us the results. I have the Dynam­ic Array Excel, which means that Excel spilled the results. We can see that How to train a Drag­on is in list B with num­ber 5 which means that it’s on posi­tion num­ber 5 in list A. We also have Spi­der-man on posi­tion num­ber 7. If we want to extract those num­bers, we have to use the AGGREGATE func­tion. In the first argu­ment of the func­tion, we have to use a func­tion num­ber 15, as this func­tion under­stands an array for­mu­la. Then, in the sec­ond argu­ment, we have to write 6 to ignore errors. At the end, in the k argu­ment let’s write ROWS from E2 to E2 and press F4 key to lock only the first one. This will allow the for­mu­la to expand while copy­ing down (Fig. 2)

=AGGREGATE(15,6,MATCH($C$2:$C$9,$A$2:$A$11,0),ROWS($E$2,E2))

Now, we have the results, how­ev­er there are some errors. To remove that, we can add the INDEX func­tion and write list A in the first argu­ment, then press F4 key to lock it (Fig. 3)

=INDEX($A$11:$A$11,AGGREGATE(15,6,MATCH($C$2:$C$9,$A$2:$A$11,0),ROWS($E$2,E2))

Now, we have the ele­ments that are on both lists. We can remove the errors. Let’s add the IFERROR func­tion (Fig. 4)

=IFERROR(INDEX($A$11:$A$11,AGGREGATE(15,6,MATCH($C$2:$C$9,$A$2:$A$11,0),ROWS($E$2,E2))),“”)

We have our results in the tar­get list, and there is still some back­up space in case we have some more results (Fig. 5)

Now, we can cre­ate a for­mu­la that will look up ele­ments only from list B. Let’s start with giv­ing each ele­ment a num­ber. We’ll use the ROW func­tion here and write the whole list as the first argu­ment. Then F4 key to lock it (Fig. 6)

=ROW($C$2:$C$9)

As we can see, Excel has spilled the results and each ele­ment has got its own num­ber. How­ev­er, we want to start from 1, not from 2 (Fig. 7)

To do this, we have to sub­tract the head­er row and press F4 key to lock it (Fig. 8)

=ROW($C$2:$C$9)-ROW($C$1)

And we have all ele­ments num­bered from 1 to 8. Now, we want only num­bers of ele­ments that aren’t on list A. How can we do it? We can divide our list by the MATCH func­tion. In the first argu­ment, we write list B and press F4 key to lock it, then list A in the sec­ond argu­ment, as that’s the place where we will be look­ing up our val­ues. Then F4 key. As the final step, we write 0, as we want to have the exact match and the look­ing up process will start from the top (Fig. 9)

=(ROW($C$2:$C$9)-ROW($C$1))/MATCH($C$2:$C$9,$A$2:$A$11,0)

Just like that we have num­bers only on those posi­tions that are on both lists. How­ev­er, we want a reverse sit­u­a­tion, where we will have num­bers in #N/A posi­tions. To to that we have to put our MATCH func­tion into the ISNA func­tion (Fig. 10)

=(ROW($C$2:$C$9)-ROW($C$1))/ISNA(MATCH($C$2:$C$9,$A$2:$A$11,0))

If the MATCH func­tion returns an error, the ISNA func­tion will change this error into TRUE. And a num­ber divid­ed by the val­ue of TRUE will change the TRUE val­ue into 1. If the ISNA func­tion returns FALSE, the math­e­mat­i­cal oper­a­tion will change FALSE into 0. Since we can­not divide by 0, we will have #DIV/0! errors (Fig. 11)

Since we have a list with num­bers which are the posi­tions of ele­ments we want to extract and errors, it’s time to put our whole for­mu­la into the AGGREGATE func­tion. Just like before, we start from the small­est val­ue, so let’s write 15, then 6, as we want to ignore errors. At the end of our func­tion, we write the ROWS func­tion with G2 cell. Then F4 key to lock the first one (Fig. 12)

=AGGREGATE(15,6,(ROW($C$2:$C$9)-ROW($C$1))/ISNA(MATCH($C$2:$C$9,$A$2:$A$11,0)),ROWS($G$2:G2))

Now, we have posi­tions with­out errors, which means that we can add the INDEX func­tion to our for­mu­la. As we take posi­tions from list B, we have to select the list, then press F4 key to lock it (Fig. 13)

After press­ing Enter, or Ctrl + Enter, we have our results. Now, we can add the IFERROR func­tion to put emp­ty text strings in the places where the for­mu­la returns errors (Fig. 14)

=IFERROR(AGGREGATE(15,6,(ROW($C$2:$C$9)-ROW($C$1))/ISNA(MATCH($C$2:$C$9,$A$2:$A$11,0)),ROWS($G$2:G2)),“”)

And we have our results (Fig. 15)

This way, we have two for­mu­las. The first one to extract ele­ments that are on both lists, and the sec­ond one to extract ele­ments that are only on one list. 

https://www.youtube.com/watch?v=gb7zo6lPZSs&t