Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Offset in Excel
  • How to retrieve first match between two ranges in Excel
  • Left Lookup in Excel
  • How to use Excel OFFSET function
  • How to get address of first cell in range in Excel

Data Analysis Examples

  • Excel Line Chart
  • Number and Text Filters Examples in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • Conditional Formatting Data bars Examples in Excel

Data Validation Examples

  • Excel Data validation must not contain
  • How To Create Drop-down List in Excel
  • Data validation must not exist in list
  • Excel Data validation number multiple 100
  • Excel Data validation allow weekday only

Highlight 3 smallest values with criteria in Excel

by

This tutorial shows how to Highlight 3 smallest values with criteria in Excel using the example below;

Formula

=AND(A1=criteria,B1<=SMALL(IF(criteria,values),3))

Explanation

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:

=AND($B3=$G$3,$C3<=SMALL(IF(color=$G$3,amount),3))

Where “color” is the named range B5:B12 and “amount” is the named range C5:C12.

How this formula works

Inside the AND function there are two logical criteria. The first is straightforward, and ensures that only cells that match the color in G3 are highlighted:

$B3=$G$3

The second logical is more complex. It’s an array formula that filters all amounts to make sure that only amounts associated with the color in G3 are highlighted:

$C3<=SMALL(IF(color=$G$3,amount),3)

The filtering is done with the IF function here:

IF(color=$G$3,amount)

The value from the amount column only survives if the color in column B matches the value in G3. The resulting array looks like this:

{FALSE;100;FALSE;200;FALSE;300;FALSE;400;FALSE;500}

and goes into the SMALL function with a k value of 3.

SMALL returns the “3rd smallest” value and only values less than or equal to this value return true.

When both logical conditions are return TRUE, the conditional formatting is triggered and cells are highlighted.

Note: this is an array formula, but it doesn’t require control + shift + enter like an array formula entered directly on a worksheet.

Post navigation

Previous Post:

Conditional formatting column is blank in Excel

Next Post:

Conditional formatting date past due in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Remove first character in a cell in Excel
  • Remove text by matching in a cell in Excel
  • FIND, FINDB functions: Description, Usage, Syntax, Examples and Explanation
  • Split dimensions into three parts in Excel
  • How to Check If A Cell Contains Specific Text in Excel
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Get last weekday in month in Excel
  • Get project end date in Excel
  • How to show last updated date stamp in Excel
  • Convert text to date in Excel
  • PMT function: Description, Usage, Syntax, Examples and Explanation
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • Calculate loan interest in given year in Excel
  • How to calculate compound interest in Excel
  • DDB function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles