Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Excel Frequency Function Example
  • How To Insert and Customize Sparklines in Excel
  • Excel Pie Chart
  • Conditional Formatting Color Scales Examples in Excel
  • How to calculate average last N values in a table in Excel

References

  • Perform case-sensitive Lookup in Excel
  • How to use Excel ROW Function
  • Get nth match with INDEX / MATCH in Excel
  • Left Lookup in Excel
  • How to get last row in text data in Excel

Data Validations

  • Excel Data validation must contain specific text
  • Excel Data validation don’t exceed total
  • Excel Data validation date in specific year
  • Excel Data validation exists in list
  • Excel Data validation require unique number

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:

VLOOKUP without #N/A error in Excel

Next Post:

How to Create Gantt Chart 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

Logical Functions

  • Check multiple cells are equal in Excel
  • Return blank if in Excel
  • IF with boolean logic in Excel
  • SWITCH function example in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • List holidays between two dates in Excel
  • Display the current date and time in Excel
  • Get last day of month in Excel
  • Display the current date in Excel
  • How to get Holiday Date from Year in Excel

Grouping

  • Group numbers at uneven intervals in Excel
  • Categorize text with keywords in Excel
  • Running count group by n size in Excel
  • If cell contains one of many things in Excel
  • Map inputs to arbitrary values in Excel

General

  • Find, Select, Replace and Go To Special in Excel
  • 3D SUMIF for multiple worksheets in Excel
  • How to calculate percent variance in Excel
  • Print Excel Sheet In Landscape Or Portrait
  • Select, Insert, Rename, Move, Delete Worksheets in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning