Explanation
In this example, the goal is to apply AND and OR logic to an array using the AND function and the OR function . The challenge is that the AND function and the OR function both aggregate values to a single result. This means you can’t use them in an array operation where the goal is to return more than one result. One workaround to this limitation is to use the MAP function, as explained below. All data is in an Excel Table named data in the range B5:C15.
AND and OR limitations
In this example, we want to test each row in the table with the following logic: Color is “Red” OR “Blue” AND Qty > 10. For each row in the table, we want a TRUE or FALSE result. If we try to use a formula like this:
=AND(OR(data[Color]="red",data[Color]="blue"),data[Qty]>10)
The formula will fail because the AND function and the OR function both aggregate values to a single result.
MAP function
One solution to implementing the logic above is to use the MAP function . The MAP function “maps” a custom LAMBDA function to each value in a supplied array . The LAMBDA is applied to each value, and the result from MAP is an array of results with the same dimensions as the original array. The MAP function is useful when you want to process each item in an array individually, but as an array operation that yields an array result.
In this example, we supply the MAP function with two arrays: data[Color] , and data[Qty] :
=MAP(data[Color],data[Qty],
Next, we need supply a LAMBDA function that implements the logic we need:
=MAP(data[Color],data[Qty],LAMBDA(a,b,AND(OR(a="red",a="blue"),b>10)))
Notice that inside the LAMBDA function, data[Color] becomes “a”, and data[Qty] becomes “b”. These names are arbitrary and you can use any valid name you like . The arrays provided to the MAP function are named by the parameters in the LAMBDA in the order that they appear.
The MAP function works through each value in data[Color] and data[Qty] and implements the logic created by the AND and OR functions. Since there are 11 rows in the table, the result is an array of 11 TRUE and FALSE values like this:
{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
These values are returned to cell E5 and spill into the range E5:E15.
Counting results
The formula in cell G5 shows a practical application of the MAP formula explained above:
=SUM(MAP(data[Color],data[Qty],LAMBDA(a,b,--AND(OR(a="red",a="blue"),b>10))))
Here, the goal is to count all TRUE results from MAP. To do that, we add a double negative (–) before the AND function to convert TRUE and FALSE values to 1s and 0s, then we nest the entire formula inside the SUM function . The MAP function returns the numeric array to SUM:
=SUM({0;1;0;1;0;0;0;1;0;0;0}) // returns 3
The SUM function then returns a final result of 3.
Explanation
The goal in this example is to return the minimum value that is unique, i.e. the minimum value that occurs only once in the data.
The UNIQUE function, new in Excel 365 , will return a unique list of values from a set of data. By default, this is a list of any value that occurs one or more times in the data.
UNIQUE has an optional third argument called exactly_once that will limit results to values that occur once only in the source data. To enable this feature, the argument needs to be set to TRUE or 1.
Working from the inside out, the UNIQUE function is configured like this:
UNIQUE(data,0,1)
For array , we provide the named range data . For the by_col argument, we use zero (0), since we want unique values by rows, not columns. Finally, for exactly_once , we provide 1, since we want only values that occur just once in the source data.
Configured this way, UNIQUE returns the 4 values that appear only once:
{700;600;500;300} // result from unique
This array is returned directly to the MIN function, which returns the minimum value, 300, as the final result:
=MIN({700;600;500;300}) // returns 300
Array formula with COUNTIF
If you are using a version of Excel without the UNIQUE function, you can find the minimum unique value with an array formula based on the COUNTIF, MIN, and IF functions.
{=MIN(IF(COUNTIF(data,data)=1,data))}
This is an array formula and must be entered with control + shift + enter, except in Excel 365 .
Working from the inside out, the COUNTIF function is used to generate a count of each value in the data like this:
COUNTIF(data,data) // count all values
Because there are 10 values in the named range data , COUNTIF returns an array of 10 results:
{2;1;1;2;1;2;2;2;1;2}
This array holds the count of each value. Next we test the array for values equal to 1:
{2;1;1;2;1;2;2;2;1;2}=1
Again, we get an array with 10 results:
{FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}
Each TRUE value corresponds to a value in the source data that occurs just once. This array is delivered directly to the IF function , which uses it like a filter. Only values in data associated with TRUE make it into the array returned by IF, all other values are FALSE.
{FALSE;700;600;FALSE;500;FALSE;FALSE;FALSE;300;FALSE}
This array is returned directly to the MIN function which automatically ignores logical values and returns the minimum of remaining values, 300, as a final result.
