Notice that the order of the values in each list is random.
In column E, we want to display a count of the common values.
The first step in this process is to name some variables.
First, the variablelist1is declared.
it’s possible for you to think of an array as a range of values without an address.
The next step in the formula is to identify common values.
The numbers represent the location of values thatwere found.
For example, looking at the first four values in the array:
And so on.
However, the numbers and errors have been replaced by TRUE and FALSE values.
A TRUE indicates a value that was found, and a FALSE indicates a value that was not found.
This is exactly what we need for the FILTER function.
Instead of joining the values incommonwith TEXTSPLIT, we count them with COUNTA.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
It is a more robust and flexible successor to the MATCH function.
XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?)
LET Function
The Excel LET function lets you define named variables in a formula.
Values can be sorted by one or more columns.
SORT returns a dynamic array of results.
Related videos
FILTER function basic example
