We are going to use Classic Excel, Legacy Excel and a classic formula with the AGGREGATE function. It means that this formula will work from 2010 and thanks to this, we won’t need to use the Crtl + Shift + Enter key combination.
Let’s start with finding elements. We will be checking whether elements from My son’s favorite movies list are on My daughter’s favorite movies list. We can use the MATCH function here. We are looking up elements from my son’s list (list B) on my daughter’s list (list A). We’re clicking 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)
Fig. 1 MATCH function
Excel has just given us the results. I have the Dynamic Array Excel, which means that Excel spilled the results. We can see that How to train a Dragon is in list B with number 5 which means that it’s on position number 5 in list A. We also have Spider-man on position number 7. If we want to extract those numbers, we have to use the AGGREGATE function. In the first argument of the function, we have to use a function number 15, as this function understands an array formula. Then, in the second argument, we have to write 6 to ignore errors. At the end, in the k argument let’s write ROWS from E2 to E2 and press F4 key to lock only the first one. This will allow the formula to expand while copying down (Fig. 2)
Now, we have the results, however there are some errors. To remove that, we can add the INDEX function and write list A in the first argument, then press F4 key to lock it (Fig. 3)
We have our results in the target list, and there is still some backup space in case we have some more results (Fig. 5)
Fig. 5 Results with extra space
Now, we can create a formula that will look up elements only from list B. Let’s start with giving each element a number. We’ll use the ROW function here and write the whole list as the first argument. Then F4 key to lock it (Fig. 6)
=ROW($C$2:$C$9)
Fig. 6 ROW function
As we can see, Excel has spilled the results and each element has got its own number. However, we want to start from 1, not from 2 (Fig. 7)
Fig. 7 Numbers for each element
To do this, we have to subtract the header row and press F4 key to lock it (Fig. 8)
=ROW($C$2:$C$9)-ROW($C$1)
Fig. 8 Subtracting the header row
And we have all elements numbered from 1 to 8. Now, we want only numbers of elements that aren’t on list A. How can we do it? We can divide our list by the MATCH function. In the first argument, we write list B and press F4 key to lock it, then list A in the second argument, as that’s the place where we will be looking up our values. Then F4 key. As the final step, we write 0, as we want to have the exact match and the looking up process will start from the top (Fig. 9)
Just like that we have numbers only on those positions that are on both lists. However, we want a reverse situation, where we will have numbers in #N/A positions. To to that we have to put our MATCH function into the ISNA function (Fig. 10)
If the MATCH function returns an error, the ISNA function will change this error into TRUE. And a number divided by the value of TRUE will change the TRUE value into 1. If the ISNA function returns FALSE, the mathematical operation will change FALSE into 0. Since we cannot divide by 0, we will have #DIV/0! errors (Fig. 11)
Fig. 11 #DIV/0! errors
Since we have a list with numbers which are the positions of elements we want to extract and errors, it’s time to put our whole formula into the AGGREGATE function. Just like before, we start from the smallest value, so let’s write 15, then 6, as we want to ignore errors. At the end of our function, we write the ROWS function with G2 cell. Then F4 key to lock the first one (Fig. 12)
Now, we have positions without errors, which means that we can add the INDEX function to our formula. As we take positions from list B, we have to select the list, then press F4 key to lock it (Fig. 13)
Fig. 13 INDEX function
After pressing Enter, or Ctrl + Enter, we have our results. Now, we can add the IFERROR function to put empty text strings in the places where the formula returns errors (Fig. 14)
This way, we have two formulas. The first one to extract elements that are on both lists, and the second one to extract elements that are only on one list.
Today, we want to compare two lists. Being more precise, we want to find elements that are on both lists, as well as elements that are only on one list.
Compare 2 lists dynamic array formula
This time, we will use Dynamic Array formulas. It makes the operation easier than using Legacy Excel formulas.
We’re starting with the XMATCH function. In the first two arguments, we write a list of elements that we are looking for, and the list that those elements are looked up in (Fig. 1)
=XMATCH(C2:C9,A2:A11)
Fig. 1 XMATCH function
In the results, we can see that we have #N/A errors in the places where Excel didn’t find any match, and numbers where it found the proper match (Fig. 2)
Fig. 2 Errors and matches
Now, we want to extract the values where Excel has found the match. To to this, we can add the ISNUMBER function to the XMATCH function (Fig. 3)
=ISNUMBER(XMATCH(C2:C9,A2:A11))
Fig. 3 ISNUMBER function added to XMATCH function
Now, our results are FALSEs and TRUEs. What we can do now, is add the FILTER function. In the first argument, we have to write the array with elements that we are looking for (Fig. 4)
=FILTER(C2:C9, ISNUMBER(XMATCH(C2:C9,A2:A11))
Fig. 4 FILTER function
And we have our results. Now, we want to find elements that are only on the My son’s list. All we need to do, is take the same formula and change the ISNUMBER function into ISNA function (Fig. 5)
=FILTER(C2:C9, ISNA(XMATCH(C2:C9,A2:A11))
Fig. 5 ISNUMBER function into ISNA function
And just like that we have found elements that are on both lists and elements that are only on one list (Fig. 6)
How can we check whether the given time is between the working hours or not?
Is this Time between Working Hours
In our case, work starts at 9:30 AM, and ends at 5:30 PM. Our given time, which is 6:47 isn’t between the working ours. When we change it, we can see that the color changed into red and ‘No’ turned into ‘Yes’, which means that now the time is between the working ours (Fig. 1)
Fig. 1 Our given time is between the working hours
However, when we go to earlier hours we can see that the details changed (Fig. 2)
Fig. 2 Our given time isn’t between the working hours
How can we check our time? The simplest solution is using the MEDIAN function that returns the value in the middle. Since all our times are in one row, we can just select the range (Fig. 3)
=MEDIAN(B2:D2)
Fig. 3 Selecting the whole range
If our data is in separate cells, we can just add those cells one by one (Fig. 4)
=MEDIAN(B2,C2,D2)
Fig. 4 Selecting cells one by one
And just like that we receive the value in the middle. If this value is equal to the given time, it means it’s between the working hours. What’s curious about our median is the fact that it won’t cross our thresholds, i.e. our starting time and finishing time. It means that the smallest value our MEDIAN function can show is the starting hour, and the largest is the finishing hour (Fig. 5)
Fig. 5 The smallest value
Now, we have to check whether our time is between the working hours. We have to compare the function to the given time (Fig. 6)
=MEDIAN(B2,C2,D2)=B2
Fig. 6 Comparing
As we can see, our logical test returned FALSE (Fig. 7)
Fig. 7 FALSE
If the logical test returns TRUE, it means that the given time is between the working ours (Fig. 8)
Fig. 8 TRUE
The answers we have are logical. Let’s make them more human like by adding the IF function to our MEDIAN logical test and some simple text like ‘Yes’ and ‘No’ (Fig. 9)
=IF(MEDIAN(B2,C2,D2)=B2,“Yes”,“No”)
Fig. 9 Adding the IF function
We can see that we can check whether our given time is between the working ours or no (Fig. 10)
If you want to calculate a median value with a condition, you cannot use a simple MEDIAN function because it needs only numbers. We have to use another function that will reduce the number of wages we look at.
Median with condition
Typically, when we want to add a condition to another function we can use the IF function and make a logical test. In our case, the logical test will be simple. We just have to check each cell in the Department column whether it’s equal to the name of the Department we look at now. Since we are comparing a single cell to a whole range, Excel will check every single cell in a given range (Fig. 1).
Fig. 1 Comparing a single cell to a range of cells
If a cell in the range is equal to the target cell, we want the function to return proper wages, i.e. the wages from the same row. If the logical test result is FALSE, we want to have something that the MEDIAN function will ignore which, in most cases, is an empty text string. However, I want to show you that the IF function really returns something so let’s write “no” (Fig. 2).
=IF($B$2:$B$13=E2,$C$2:$C$13,“no”)
Fig. 2 IF function
We can see that Dynamic Array Excel spilled the results and we have wages only for the departments we chose. For other departments we have ‘no’. Now, we can put this function into the MEDIAN function (Fig. 3).
=MEDIAN(IF($B$2:$B$13=E2,$C$2:$C$13,“no”))
Fig. 3 MEDIAN function with IF function
Now, we’ve calculated the median value with a condition for each department. We can see that for HR it’s $4,000, for IT it’s $8,100 and for Marketing it’s $5,900. Those are the middle values for each department (Fig. 4).
Sometimes, we want to find all the matched values and combine them into one text string, i.e., we want o VLOOKUP all matched values and then combine them.
VLOOKUP all matches and combine them
I will give you two solutions. The first solution will work from Excel 2019, and the second one will work in Dynamic Array Excel. First of all, we have to check if our representative comes from the country we are looking at. Let’s take a representative from Spain. There is only one representative of Spain in our example. We can check it using the IF function, where we have to create a proper logical test. We have to take into consideration the Country column, so let’s write it by selecting the column and clicking the F4 key to lock it. Then, we have to check if the values from the column are equal to just one country (Spain) by clicking the cell with the name of Spain (D4). It means that cell D4 will be compared to each cell from the Country column individually (Fig. 1)
Fig. 1 Comparison to each cell
The logical test will return TRUE only in the case of Spain. In the case of other countries, it will return FALSE. If the logical test is in an array, we can choose proper, corresponding result as a return value. We just need to select an array of the same size, which, in our case, is the Representative column. Then, we click the F4 key to lock it. When the logical test returns FALSE, we want to see an empty text string, so we have to write a space in double quotes. Let’s close the function with a parenthesis (Fig. 2)
=IF($A$2:$A$14=D2,$B$2:$B$14,” ”)
Fig. 2 IF function
Since I’m working in Dynamic Array Excel, Excel spills the results to the whole column (Fig. 3).
Fig. 3 IF function results
We can see that we have only one representative of Spain, and many empty cells. If we want to find the remaining representatives, we have to join the information we have. To do that, we can use the TEXTJOIN function, which is available from Excel 2019. We have to give the function a delimiter. Let’s write a comma and a space in double quotes. Then, we have to decide whether we want to ignore empty cells. We definitely do, so let’s write TRUE, or its shorter version, which is 1. Then, we just have to close the whole formula once again (Fig. 4).
After entering the formula and copying it down, we have our results (Fig. 5).
Fig. 5 TEXTJOIN function results
When we are in Dynamic Array Excel, we can use the FILTER function instead of the IF function. This function just filters our data. Let’s write FILTER function. Our data is located only in one column, which is the Representative column, so we have write the range and lock it with the F4key. Then, we have to create an array of the same size with logical values of TRUE or FALSE. It means that we have to create the same logical test as we did with the IF function. Let’s add the Country column (A2:A14), F4key to lock it, then an equal sign to check whether any of the value from the Country column is equal to Spain (D2). After closing it, we have a ready formula (Fig. 6).
=FILTER($B$2:$B$14,A$2:$A$14,=D2)
Fig. 6 FILTER function
We can see that Spain has only one representative, but Poland has got three (Fig. 7).
Fig. 7 FILTER function results
That’s why we have to put our FILTER function results into the TEXTJOIN function. And, once again, we have to write a comma and a space in double quotes. Then, we have to decide that we want to ignore empty cells and close the whole formula (Fig. 8)
=TEXTJOIN(“, “,1,FILTER($B$2:$B$14,A$2:$A$14=D2))
Fig. 8 TEXTJOIN with FILTER function
We can see that the results in the first solution and in the second solution are the same (Fig. 9)
