## 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*.