Highlight 3 smallest values with criteria

★ 15 minutes read

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

Formula

=AND(cell1=criteria,cell2<=SMALL(IF(criteria,values),3))

Explanation

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. 

Example 1

In the following example we will see how to highlight 3 smallest values with criteria Mango fruit:

=AND($D4=$H$4,$E4<=SMALL(IF(fruits=$H$4,price),3))

 

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:

$D4=$H$4

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:

IF(fruits=$H$4,price)

The resulting array looks thus:

{FALSE;50;FALSE;30;FALSE;100;FALSE;20}

Thereafter, it goes into the “SMALL” function with an “n” value of 3 to give:

$E4<=SMALL(IF(fruits=$H$4,price),3)

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.

Notes

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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar