Highlight 3 smallest values with criteria

To highlight the 3 smallest values that meet specific criteria, you can use an array formula based on the AND and SMALL functions. In the example shown, the formula used for conditional formatting is:
Where "color" is the named range B5:B12 and "amount" is the named range C5:C12.
Inside the AND function there are two logical criteria. The first is straightforward, and ensures that only cells that match the color in E5 are highlighted:
$B3=$E$5
The second test is more complex:
Here, we filter amounts to make sure that only values associated with the color in E5 (blue) are retained. The filtering is done with the IF function like this:
IF(color=$E$5,amount)
The resulting array looks like this:
{FALSE;100;FALSE;200;FALSE;300;FALSE;400;FALSE;500}
Notice the value from the amount column only survives if the color is "blue". Other amounts are now FALSE.
Next, this array goes into the SMALL function with a k value of 3, and SMALL returns the "3rd smallest" value, 300. The logic for the second logical test reduces to:
$C3<=300
When both logical conditions are return TRUE, the conditional formatting is triggered and cells are highlighted.
Note: this is an array formula, but does not require control + shift + enter.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.