Just as it is with using Excel to solve many things, you can also use it to highlight 3 smallest values with criteria. This post will guide you on how to highlight 3 smallest values with criteria using a formula approach in Excel.
Highlight 3 smallest values with criteria
The general formula is showing how to Highlight 3 smallest values with criteria. With specific criteria, you can use an array formula based on the AND and SMALL functions to highlight 3 smallest values from a list.
In the following example we will see how to highlight 3 smallest values with criteria Mango fruit:
Figure 1: Highlight 3 smallest values with criteria
In formula syntax “fruits” and “price” are named range D4:D14 and E4:E14 respectively.
There are two logical criteria in the AND function. The first one is simple to understand. It highlights only cells that correspond to the fruit in the cell H4:
The second logical is a bit complex. It filters all prices using the “IF” function to ensure that only prices that correspond with the color in H4 are highlighted:
The resulting array looks thus:
Thereafter, it goes into the “SMALL” function with an “n” value of 3 to give:
Then “SMALL” function returns the 3 smallest values from the array.
When the logical conditions have value TRUE, the conditional formatting highlight the cells that contain the smallest values based on the criteria.
To highlight 3 smallest values with criteria you will have to use an array formula, but you don’t have to use control + shift + enter as for other array formulas typed directly in Excel Worksheet.