When we want to find an intersection between a row and a column basing on headers, we can do it in two different ways. The first one will be in Legacy Excel, and the second in Dynamic Array Excel.
The first solution uses the MATCH function as well as its close friend, the INDEX function.
Let’s start with looking for the country. In this case we want to find the row number. Let’s write the cell, the range where we will be looking for and 0 for an exact match (Fig. 1)
=MATCH(B8,A3:A6,0)
Fig. 1 MATCH function for countries
We also want to find the column number. In this case we also use the MATCH function. Let’s the cell, the range where we will be looking for and 0 for an exact match (Fig. 2)
=MATCH(B9,B2:G2,0)
Fig. 2 MATCH function for months
When we have our row and column numbers, we can use the INDEX function, select the whole dataset, then write the row and column numbers (Fig. 3)
=INDEX(B3:G6,D8,D9)
Fig. 3 INDEX function
Just like that, we did the 2D lookup, or we have found the values we wanted (Fig. 4)
Fig. 4 Results
So far, we have had three formulas. If we want to have only one, we need to implement some modifications. We have to copy our MATCH function and paste it in the place referring to cell D8. Then, we have to copy the second MATCH function without the equal sign, and paste it in the place of the reference to cell D9. Now, we have one, bigger formula instead of three smaller ones (Fig. 5)
=INDEX(B3:G6,MATCH(B8,A3:A6,0),MATCH(B9,B2:G2,0))
Fig. 5 One, big formula
Now, let’s focus on the solution in the Array Excel. We will be using here the XLOOKUP function twice.
First, we are looking for a row in the country headers. Then, as a returning array, we give Excel the whole dataset (Fig. 6)
=XLOOKUP(B8,A3:A6,B3:G6)
Fig. 6 XLOOKUP function
Since I’m using the XLOOKUP function, and we are working in the Array Excel,Excel will return the whole row.
In such a case, we have to find a proper column. We can also do it with the XLOOKUP function. Let’s write the XLOOKUP function once again. This time we will be looking for a month in the month headers. As a return array, we have the row we found previously (Fig. 7)
=XLOOKUP(B9,B2:G2,XLOOKUP(B8,A3:A6,B3:G6))
Fig. 7 Another XLOOKUP function
And just like that, we have our results (Fig. 8)
Fig. 8 Results
Even if we change the values in our formula, the result is still correct (Fig. 9)
Fig. 9 Checking the formula
The results are correct both in XLOOKUPs and INDEX with MATCH functions.
Today, we want to learn about sorting by months with the SORT function.
SORT by months using functions
Let’s start with writing the SORT function, with an array as the first argument. In the second argument we need to write 2, as we want to sort by months, which are located in the second column. Now, we can close the function (Fig. 1).
=SORT(A2:C145,2)
Fig. 1 SORT function
Just like that the SORT function sorted data by the Month column. It’s important that the SORT function doesn’t consider the custom list. Excel does it, but the SORT function doesn’t. Now. let’s have a look at how we can properly sort by months using the SORT function. First of all, we cannot use the SORT function, but the SORTBY function. In this function, even if we change the second argument to a month column, we still get an ascending sorting, from A to Z. It means that the sorting is based on the alphabet, not on months of the year. The thing is we cannot do the sorting based on arguments. We need to use the MATCH function. It will look up a given month in a list of months. In the third argument we write 0, as we want to have the exact match (Fig. 2)
=MATCH(B2,$J$2:$J$13,0)
Fig. 2 MATCH function
The MATCH function starts the sorting from the top on the Month column. As we can see in column D, we have a proper number for each month. The column acts as a helper column, however, sometimes we don’t want any helper column, so we can create a helper column in our formula. We can match each month from the Month column (column B) using the MATCH function, where we need to write the lookup value in the first argument, which is our data column. In the second argument, we need to write the whole list of months on the right, then let’s write 0 as the exact match (Fig. 3)
SORTBY(A2:C145,MATCH(B2:B145,J2:J13,0))
Fig. 3 SORTBY function
It works. We see that the SORTBY function found a proper numbers for each month. It’s important that it sorts by months, not by years. However, we can add the year as well. But, I want to show you something else. First of all, if we don’t like the custom helper column list, we can just click F9 in the lookup array argument to hard code the array list in our formula (Fig. 4)
We can even delete the custom column. As we can see, it works properly. When we look at column D, we can see #N/A error. It’s because the MATCH function is trying to find months in the custom column cells which are empty (Fig. 5)
=MATCH(B2,$J$2:$J$13,0)
Fig. 5 Empty cells in the Month column
Now, let’s focus on dates based on years and months. Sometimes, our date in a dataset isn’t written only in one column, but it can be separated into the year, month and day columns. We can create a full date from the data we have. We will try it in the Helper column. First of all, we write =, then 1 combined with a month, combined with a year (Fig. 6)
=1&B2&A2
Fig. 6 Creating a date
And just like that, we combined the 1st of January 2022. Our date, however, is text. We want Excel to understand it as a date, so we just need to add parentheses and a 0 (Fig. 7)
=(1&B2&A2)+0
Fig. 7 Changing text into numbers
Excel magic has just happened! It changed text into numbers. Now, to change the numbers into dates, we need to go to the Home tab (1), then change the formatting (2) into Short Date (3) (Fig. 8)
Fig. 8 Numbers into a date
Since we have dates based on the year and the month columns, we can sort them. Let’s write the SORTBY function. In the first argument we write the data column, and in the second column we have to combine 1 with the Month column, then with the Year column. When we close the parentheses we need to add 0 to change text values into numbers (Fig. 9)
=SORTBY(A2:C145,(1&B2:B145&A2:A145)+0)
Fig. 9 SORTBY function
Now, we have only six Januaries at the beginning, because we have have only six Januaries in 2022. Next six Januaries are in 2023. This way we sorted our data by months and years using a helper column.
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)
In the previous post, I was talking about a dominant, which is the most frequent number. Today, we are going to find the most frequent text.
Most frequent text
This task is a bit harder, because the MODE function that we used previously needs numbers. It means that we have to convert text into numbers. To do so, we can use the MATCH function. It means that we will be looking for our whole text in our whole text. We want the exact match (Fig. 1)
Fig. MATCH function to look for the exact match
While using the MATCH function, Excel starts calculations from the top. It means that each puppy in the range is referring to the first one on the list, which is on the first position is presented as 1 (Fig. 2)
Fig. 2 Puppy gets number 1
Looking at the second example, the first kitten is in position number 2, so each kitten will refer to the same number (Fig. 3)
Fig. 3 Kitten gets number 2
Now, that we have our text connected to numbers, we can add the MODE.MULT function to return the most frequent numbers, i.e. positions of text (Fig. 4)
=MODE.MULT(MATCH(A2:A9,A2:A9,0))
Fig. 4 MODE.MULT function to return the most frequent numbers
And we have the results (Fig. 5)
Fig. 5 Results
Since we have the positions, we can add the INDEX function to return text connected to those positions (Fig. 6)
=INDEX(A2:A9,MODE.MULT(MATCH(A2:A9,A2:A9,0)))
Fig. 6 INDEX function to return text
Just like that we have our solutions in the Dynamic Array Excel (Fig. 7)
Fig. 7 Solutions in Dynami Array
In the classic Legacy Excel, we should select more cells and use the Ctrl + Shift + Enter key combination to put our results in all cells (Fig. 8)
Fig. 8 Key combination
However, we are in DA Excel, so we have a simpler solution. At the end, we want to check what happens when the text appears only once in a range. We can see that the MODE.MULT function returns the #N/A error (Fig. 9)
Fig. 9 #N/A error
When text appears the same number of times, in our case it’s cat and dog, the MODE.MULT function will return those results (Fig. 10)
