Lesson#259: Using SMALL Function with IF Condition in Excel (Based on Groups)

Excel is a powerful tool when it comes to analyzing datasets. Often, we need to extract the smallest value from a list, but not from the entire dataset—rather from a specific group of numbers that we define. This is where the combination of the SMALL function with IF and MATCH comes into play.

In this article, we’ll learn how to:

Understand the SMALL function
Apply conditions with IF + MATCH
Extract the smallest values from custom groups like 1,5,9 | 2,6,10 | 3,7,11 | 4,8,12
Explore practical examples

🔸 1. What is the SMALL Function?

The SMALL function in Excel returns the k-th smallest value from a dataset.

Syntax:

=SMALL(array, k)

array → the range of data
k → the position of the smallest number (1 for smallest, 2 for 2nd smallest, etc.)

Example:

=SMALL({10, 20, 5, 8}, 1)   → returns 5
=SMALL({10, 20, 5, 8}, 2)   → returns 8

🔸 2. Adding Conditions to SMALL

By default, SMALL looks at the entire dataset. But what if you want only the smallest value from a specific group of numbers (like 1,5,9)?

Here, we use an IF + MATCH condition inside SMALL.

🔸 3. Formula for Group-Based Smallest Value

Suppose our dataset is in C2:C20. To extract the smallest value belonging to the group 1,5,9, we use:

=SMALL(IF(ISNUMBER(MATCH(C2:C20,{1,5,9},0)),C2:C20),1)

👉 How it works:

MATCH(C2:C20,{1,5,9},0) → checks whether each value in C2:C20 belongs to the group {1,5,9}
ISNUMBER(...) → converts TRUE/FALSE depending on match
IF(...,C2:C20) → keeps only numbers that belong to the group, ignores others
SMALL(...,1) → finds the smallest among them

⚠️ In older versions of Excel, press Ctrl+Shift+Enter to activate this as an array formula. In Excel 365 or Excel 2021, just hit Enter.

🔸 4. Group-Specific Formulas

For 1,5,9:

=SMALL(IF(ISNUMBER(MATCH(C2:C20,{1,5,9},0)),C2:C20),1)

For 2,6,10:

=SMALL(IF(ISNUMBER(MATCH(C2:C20,{2,6,10},0)),C2:C20),1)

For 3,7,11:

=SMALL(IF(ISNUMBER(MATCH(C2:C20,{3,7,11},0)),C2:C20),1)

For 4,8,12:

=SMALL(IF(ISNUMBER(MATCH(C2:C20,{4,8,12},0)),C2:C20),1)

🔸 5. Example Walkthrough

Imagine you have the following dataset in column C:

Row	Value
2	9
3	6
4	12
5	5
6	11
7	1
8	10

Now:

Smallest from group 1,5,9 = 1
Smallest from group 2,6,10 = 6
Smallest from group 3,7,11 = 11
Smallest from group 4,8,12 = 12

🔸 6. Extending to 2nd Smallest, 3rd Smallest

If you want the 2nd smallest or 3rd smallest, just change the k value in the formula.

Example (2nd smallest from group 1,5,9):

=SMALL(IF(ISNUMBER(MATCH(C2:C20,{1,5,9},0)),C2:C20),2)

🔸 7. Practical Uses

This approach is highly useful when:
✔ You categorize numbers into custom groups
✔ You want to perform ranking inside groups
✔ You work with astrology, numerology, or financial cycles where numbers repeat in modular groups (1–12 system, for example)

🔸 8. Pro Tip (Dynamic Grouping)

Instead of writing separate formulas for each group, you can create a lookup table for groups and use a dynamic MATCH, so Excel automatically detects which group a number belongs to.

✅ Conclusion

The SMALL function with IF + MATCH gives you powerful control over datasets by allowing you to extract the smallest values within custom groups. This technique can be extended to find the 2nd, 3rd smallest, or even combined with LARGE for maximum values.

It is a must-know formula trick for advanced Excel users who want to filter and analyze data in modular groups such as 1–12 cycles.