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.
How to calculate a sum or an average of top 3 maximal values?
Sum of top 3 values
In Legacy Excel, we can use the LARGE function, in which we need to select the range for our calculation. In the k argument let’s write 1 (Fig. 1).
=LARGE($C$2:$C$33,1)
Fig. 1 LARGE function
After entering the function, we have the largest value (Fig. 2).
Fig. 2 The largest value
Now, we can copy the function and add the second and the third largest value (Fig. 3).
This way we have the sum of 3 largest values (Fig. 4).
Fig. 4 The sum of 3 largest values
This solution, however, is the least dynamic of all solutions we have. We can make this formula shorter by hardcoding our values. We have to write 1, 2 and 3 as an array (Fig. 5).
=LARGE($C$2:$C$33,{1;2;3})
Fig. 5 The first, second and third value written as an array
This way Excel will return three largest values. I’m using Dynamic Excel, so the results are spilled (Fig. 6).
Fig. 6 Spilled results
Now, that our values have been hard-coded, we can sum them by using the SUM function. We can also use this function to sum up averages (Fig. 7).
=SUM(LARGE($C$2:$C$33,{1;2;3}))
Fig. 7 Summing up the largest values
Our results (Fig. 8).
Fig. 8 Results
This solution, however, is hard to modify, so we can make it a bit more dynamic. We can use the ROW function and select the first three rows from the sheet. This way our solution is more dynamic (Fig. 9).
=SUM(LARGE,($C$2:$C$33,ROW(A1:A3)))
Fig. 9 A more dynamic solution
Results (Fig. 10).
Fig. 10 Results
But, in Legacy Excel, we should use the SUMPRODUCT function instead of the SUM function or use the Ctrl + Shift + Enter shortcut to put the formula into the cell (Fig. 11).
=SUMPRODUCT(LARGE,($C$2:$C$33,ROW(A1:A3)))
Fig. 11 SUMPRODUCT function
And we have the result. In the second column, in Dynamic Array Excel, we still need the SUM and LARGE functions, then the array which is the range we will look at. Then, we can use the SEQUENCE function to create a sequence of proper numbers, let’s take 3 (Fig. 12).
=SUM(LARGE($C$2:$C$33,SEQUENCE(3)))
Fig. 12 Adding the SEQUENCE function
We have proper results (Fig. 13).
Fig. 13 Results
This solution is much more dynamic. Let’s change the number into 5 (Fig. 14).
=SUM(LARGE($C$2:$C$33,SEQUENCE(5)))
Fig. 14 3 changed into 5
Here is the result (Fig. 15).
Fig. 15 Results
In Legacy Excel, we can also change one number. Let’s change the number of rows in the range from 3 to 5 (Fig. 16).
=SUMPRODUCT(LARGE($C$2:$C$33,ROW(A1:A5)))
Fig. 16 A3 into A5
And we have the same result (Fig. 17).
Fig. 17 Result
We have to remember which version of Excel we have and which solution we can use.
Today, we are going to talk about choosing a random element from a list without repeats. We have eight elements in our list. I put there the same number two times, which gives me the opportunity to choose the same number two times. If there are three the same numbers, I could choose the number up to three times. However, the choosing is random, so I don’t really know how many times I will choose it.
Random list no repeats, manual and formulas solutionsFig. 1 A list of elements
If we want to choose an element from the list at random without repeats, we can use a manual solution. We need to write the RAND function which returns numbers from 0 to 1 with fifteen digit precision. It means that it’s almost impossible to choose the same number twice (Fig. 2).
=RAND()
Fig. 2 RAND function
After dragging formula down, we have 8 random numbers. We have to remember that the RAND function is a volatile function. It means that it is recalculated every time our worksheet changes (Fig. 3).
Fig. 3 Recalculation
Now, that we have our helper column, we can do the sorting. We go to Data tab and we choose the A to Z or Z to A command.
Fig. 4 Sorting
If we want to have, e.g. five elements, we have to choose five elements from the top. It doesn’t matter whether we sort from A to Z or Z to A. We just choose the first five elements. We can see that the numbers are recalculated every time we sort our data (Fig. 5)
Fig. 5 Five elements from the list
Let’s get back to our original list and try the second solution. It is from Legacy Excel and it uses formulas. Let’s start with a way to sort our numbers. We can use, e.g. the LARGE function, which returns the biggest number from the list, then the second biggest, then the third biggest, and so on. We have to press the F4 key to lock our list. Then, we need a way to choose the first, the second, and so on numbers. So, we want to change our key argument using a proper function. One of the simplest solution I know is using the ROW function with reference to cell A1, which is free (without $ signs). When we copy our formula down, our A1 reference will also go down, and the ROW function will return the first row, the second, the third, and so on (fig. 6)
=LARGE($B$2:$B$9,ROW(A1))
Fig. 6 ROW function
After entering the formula, we have our numbers placed from the largest to the smallest. Our five elements are now in a proper order. It means that now we can choose elements. However, we must use the MATCH and INDEX function in our data orientation. Let’s add the MATCH function to find the position of our number. We have to add the range, which is our random number column and press the F4 key to lock it. Then, we are adding 0 because we want to have the exact match (Fig. 7)
=MATCH(LARGE($B$2:$B$9,ROW(A1)),$B$2:$B$9,0)
Fig. 7 MATCH function
After entering and copying down, we have positions of the largest numbers. When we look at the first position in our Chosen column, we have 5. It means that the largest number from our original list is located in the 5th place.
Fig. 8 Position of the largest number
However, we don’t want to have position numbers, but the elements connected to those numbers. It means that we have to add the INDEX function to our MATCH function. This time, we want to choose elements from our list, so we are adding the range and lock it by pressing the F4 key. It turns out that our MATCH function is the row number. We have to close the formula (Fig. 9)
After entering and copying down, we have elements selected at random without repeats. When I press the F9 key, I am forcing it to recalculate our results. Sometimes, we see our number (123) twice because it is twice on our original list (Fig. 10)
Fig. 10 Elements selected at random
It’s high time we tried the final solution in Dynamic Array Excel. This solution is simpler that the one in Legacy Excel because we just need two functions. In DA Excel, we have the RANDARRAY function which can create a random array. We just need to copy the same column into our formula, which means that we need an array with eight rows and one column. That’s why we are writing just 8 (Fig. 11).
=RANDARRAY(8)
Fig. 11 RANDARRAY function
After putting our formula into Excel, we have eight random numbers. In DA Excel, we can sort by this column. Thus, we have to add the SORTBY function to our formula. The array which we want to sort is our list. This time, we don’t have to lock it because it is a DA formula, and our formula is the result (Fig. 12).
=SORTBY(A2:A9,RANDARRAY(8))
Fig. 12 SORTBY function
After putting the formula down, we have eight elements from our list in a random order. Pressing the F9 key allows us to choose elements once again at random. Let’s choose five elements from the top once again. We still can see the remaining elements. If you don’t want to see them, you have some options. You can, for example, select five elements from our list, then press the F2 key to go to the formula edit mode, then press the Ctrl + Shift + Enter combination to create an array formula. The array formula is from Legacy Excel. Now, we have only five elements because I’ve selected only five cells. Those cells are an array, which means that we cannot change or delete any single element of the array. If you want to change it, you have to change the whole array at one time (Fig. 13).
Fig. 13 An array
Summing up, if you want to choose an element at random from a list, you can use a manual solution, a Legacy Excel solution or a Dynamic Array solution.
