Explanation
In this example, the goal is to create a formula that will extract unique values from a range of data in a case-sensitive way. Normally, we would use the UNIQUE function to extract unique values. However, UNIQUE is not case-sensitive so it won’t work in this situation. One way to solve this problem is to use the REDUCE function with a custom LAMBDA function, as explained below.
REDUCE function
The REDUCE function applies a custom LAMBDA function to each element in a given array and accumulates results to a single value. The generic syntax for the REDUCE function looks like this:
=REDUCE([initial_value], array, lambda)
The calculation performed by REDUCE is determined by a custom LAMBDA function with this generic syntax:
LAMBDA(a,v,calculation)
The first argument, a , is the accumulator. The accumulator begins as the initial_value provided to REDUCE and changes as REDUCE loops over the elements in the array and applies a calculation. The v represents the value of each element in the array. Calculation is the formula logic that creates the final accumulated result.
REDUCE + LAMBDA
In the worksheet shown, the formula in cell D5 is:
=REDUCE(,data,LAMBDA(a,v,IF(SUM(--EXACT(a,v)),a,VSTACK(a,v))))
Notice that the initial value is purposely not provided, because we want to start with a null value. The custom LAMBDA function inside of REDUCE looks like this:
LAMBDA(a,v,IF(SUM(--EXACT(a,v)),a,VSTACK(a,v)))
At a high level, the REDUCE function loops over the values in data one at a time. At each new value, v , the custom LAMBDA function checks if v is already in the accumulator, a . If v is already in a , the current value of a is returned. If v is not already present in a , the function combines a and v with the VSTACK function . The final result is an array that contains case-sensitive unique values.
Details
The EXACT function is what makes this formula case-sensitive, and the IF function is used to test values and control flow:
IF(SUM(--EXACT(a,v)),a,VSTACK(a,v))
The logical_test inside of IF is based on the EXACT function and the SUM function:
SUM(--EXACT(a,v))
Normally, the EXACT function checks if two values are exactly equal, including upper and lowercase characters. In this case, EXACT is comparing a and v . Because a is an array, the result will be an array of TRUE and FALSE values. The double negative (–) is used to convert the TRUE and FALSE values from EXACT into 1s and 0s, which are then summed by the SUM function. If SUM returns a positive number (which evaluates to TRUE in Excel), it means the v already exists in a , and IF returns a . If SUM returns zero (which evaluates to FALSE in Excel), it means v does not yet exist in a , and IF runs the VSTACK function, which is configured to combine a and v by stacking v vertically below a .
Explanation
In this example, the goal is to extract unique values from three separate ranges at the same time: range1 (C5:C16), range2 (D5:D15), and range3 (F5:F13). At one time, this was a difficult problem, since UNIQUE is programmed to accept only one array and there is no obvious way to provide another range. However, with the introduction of the VSTACK function , the solution is straightforward.
UNIQUE function
The UNIQUE function makes it very easy to extract unique values from a range. Just give UNIQUE a range, and it will give you back the unique values:
=UNIQUE(range) // extract unique
Like other dynamic array formulas , the results from UNIQUE will spill onto the worksheet into multiple cells.
The challenge in this example is to provide more than one range to UNIQUE at the same time. The solution is to use the VSTACK function to combine ranges first, before invoking UNIQUE. This is done with the VSTACK function.
VSTACK function
The VSTACK function combines arrays or ranges vertically into a single array . For example, the formula below joins range1 and range2:
=VSTACK(range1,range2) // combines ranges
Each additional array is appended to the bottom of the previous array. The result from VSTACK is a single array with range1 at the top. To combine more arrays, simply provide more arrays to VSTACK.
Note: VSTACK is currently a Beta function available only through the Beta channel of Office Insiders. The Office Insiders program is free to join in Excel 365. Without VSTACK, it is still possible to combine ranges in a formula, but it is a more complicated formula .
UNIQUE with VSTACK
To solve the problem in this example, we simply need to nest the VSTACK function inside the UNIQUE function like this:
=UNIQUE(VSTACK(range1,range2,range3))
Working from the inside out, the VSTACK function combines all three ranges vertically into a single range:
VSTACK(range1,range2,range3) // combine ranges into one
The combined array has range1 on top, range2 in the middle, and range3 at the bottom:
range1
range2
range3
This array is then delivered to the UNIQUE function, which returns the unique values in the combined range. The result is a list of unique values in all three ranges taken together.
Empty cells
If any of the ranges to be combined contain empty cells, a zero (0) will appear as a unique value in the final result. To prevent empty cells from being evaluated by UNIQUE, you can use the FILTER function like this:
=LET(
data,VSTACK(range1,range2,range3),
UNIQUE(FILTER(data,data<>""))
)
In this formula, the LET function is used to store the result from VSTACK in the variable data so that it can be used twice inside the FILTER function without another call to VSTACK. For more details on the operation of FILTER with UNIQUE, see this example .
Legacy Excel
In Legacy Excel , there is no UNIQUE function or VSTACK function, so the formula on this page is not possible. In simple scenarios, you can use a formula based on INDEX and MATCH to extract unique values. See Alternatives to Dynamic Array Functions for a more general discussion.
If you open the attached workbook in an older version of Excel without UNIQUE and/or VSTACK. You will see an xlfn prefix before the function name. The original result will still be displayed, but the formula will not update if source data changes.
