Compare 2 lists — Dynamic Array formula

by | Jul 18, 2023 | Excel

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. 

Com­pare 2 lists dynam­ic array formula

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)

XMATCH function
Fig. 1 XMATCH function

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)

Errors and matches
Fig. 2 Errors and matches

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))

ISNUMBER function added to XMATCH function
Fig. 3 ISNUMBER func­tion added to XMATCH function

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))

FILTER function
Fig. 4 FILTER function

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))

ISNUMBER function into ISNA function
Fig. 5 ISNUMBER func­tion into ISNA function

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)

Results
Fig. 6 Results

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

VLOOKUP all matches and combine them

by | Jan 3, 2023 | Excel

Some­times, we want to find all the matched val­ues and com­bine them into one text string, i.e., we want o VLOOKUP all matched val­ues and then com­bine them.

VLOOKUP all match­es and com­bine them

I will give you two solu­tions. The first solu­tion will work from Excel 2019, and the sec­ond one will work in Dynam­ic Array Excel. First of all, we have to check if our rep­re­sen­ta­tive comes from the coun­try we are look­ing at. Let’s take a rep­re­sen­ta­tive from Spain. There is only one rep­re­sen­ta­tive of Spain in our exam­ple. We can check it using the IF func­tion, where we have to cre­ate a prop­er log­i­cal test. We have to take into con­sid­er­a­tion the Coun­try col­umn, so let’s write it by select­ing the col­umn and click­ing the F4 key to lock it. Then, we have to check if the val­ues from the col­umn are equal to just one coun­try (Spain) by click­ing the cell with the name of Spain (D4). It means that cell D4 will be com­pared to each cell from the Coun­try col­umn indi­vid­u­al­ly (Fig. 1)

Comparison to each cell
Fig. 1 Com­par­i­son to each cell

The log­i­cal test will return TRUE only in the case of Spain. In the case of oth­er coun­tries, it will return FALSE. If the log­i­cal test is in an array, we can choose prop­er, cor­re­spond­ing result as a return val­ue. We just need to select an array of the same size, which, in our case, is the Rep­re­sen­ta­tive col­umn. Then, we click the F4 key to lock it. When the log­i­cal test returns FALSE, we want to see an emp­ty text string, so we have to write a space in dou­ble quotes. Let’s close the func­tion with a paren­the­sis (Fig. 2)

=IF($A$2:$A$14=D2,$B$2:$B$14,” ”)

IF function
Fig. 2 IF function

Since I’m work­ing in Dynam­ic Array Excel, Excel spills the results to the whole col­umn (Fig. 3).

IF function results
Fig. 3 IF func­tion results

We can see that we have only one rep­re­sen­ta­tive of Spain, and many emp­ty cells. If we want to find the remain­ing rep­re­sen­ta­tives, we have to join the infor­ma­tion we have. To do that, we can use the TEXTJOIN func­tion, which is avail­able from Excel 2019. We have to give the func­tion a delim­iter. Let’s write a com­ma and a space in dou­ble quotes. Then, we have to decide whether we want to ignore emp­ty cells. We def­i­nite­ly do, so let’s write TRUE, or its short­er ver­sion, which is 1. Then, we just have to close the whole for­mu­la once again (Fig. 4).

=TEXTJOIN(“, “,1,IF($A$2:$A$14=D2,$B$2:$B$14,” ”))

TEXTJOIN function
Fig. 4 TEXTJOIN function

After enter­ing the for­mu­la and copy­ing it down, we have our results (Fig. 5). 

TEXTJOIN function results
Fig. 5 TEXTJOIN func­tion results

When we are in Dynam­ic Array Excel, we can use the FILTER func­tion instead of the IF func­tion. This func­tion just fil­ters our data. Let’s write FILTER func­tion. Our data is locat­ed only in one col­umn, which is the Rep­re­sen­ta­tive col­umn, so we have write the range and lock it with the F4 key. Then, we have to cre­ate an array of the same size with log­i­cal val­ues of TRUE or FALSE. It means that we have to cre­ate the same log­i­cal test as we did with the IF func­tion. Let’s add the Coun­try col­umn (A2:A14), F4 key to lock it, then an equal sign to check whether any of the val­ue from the Coun­try col­umn is equal to Spain (D2). After clos­ing it, we have a ready for­mu­la (Fig. 6).

=FILTER($B$2:$B$14,A$2:$A$14,=D2)

FILTER function
Fig. 6 FILTER function

We can see that Spain has only one rep­re­sen­ta­tive, but Poland has got three (Fig. 7). 

FILTER function results
Fig. 7 FILTER func­tion results

That’s why we have to put our FILTER func­tion results into the TEXTJOIN func­tion. And, once again, we have to write a com­ma and a space in dou­ble quotes. Then, we have to decide that we want to ignore emp­ty cells and close the whole for­mu­la (Fig. 8)

=TEXTJOIN(“, “,1,FILTER($B$2:$B$14,A$2:$A$14=D2))

TEXTJOIN with FILTER function
Fig. 8 TEXTJOIN with FILTER function

We can see that the results in the first solu­tion and in the sec­ond solu­tion are the same (Fig. 9)

Results
Fig. 9 Results

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