Excel Find Lowest Value with Criteria [9 Effective Ways]

Finding the lowest value may be an essential task in some cases. Have you tried this basic calculation?

In this article, I’ll show nine effective ways including real-life examples, and of course, proper explanations of the formula of finding the lowest value with criteria. So that you can adjust them according to your requirements.

Download Practice Workbook

How to Enter a Formula in Excel

Do you know how can we insert a formula in Excel?

Entering a formula in the Excel formula bar is quite a simple task.

First, you have to select a blank cell where you want to show the output. Then input an equal sign (=). And then insert the formula with proper parenthesis. At last, press Enter.

More importantly, if the function is an array formula, you have to press CTRL+SHIFT+ENTER to get the output. But if you have Microsoft 365, you don’t need to do that. I have used array formulas in all methods in this tutorial. So be careful about that.

Excel Find Lowest Value with Criteria

Let’s have a look at our dataset. Some popular Name of Sites is provided along with Category. Moreover, the Number of Visits is given with the Platforms that are used for visiting.

Datase for Finding Lowest Value in Excel

Right now, we’ll identify the lowest visit value considering different criteria through using well-known Excel functions.

Let’s start.

1. Using MIN & IF Functions with Single Criteria

We may utilize the combination of MIN and IF functions to find the lowest value.

The MIN function restores the smallest value in a range of numbers. The syntax of the function is

=MIN (number1, [number2], ...)

The arguments are-

number1 – Number, reference to a numeric value, or range that contains numeric values.

number2 – [optional] Number, reference to a numeric value, or range that contains numeric values.

Again, The IF function is frequently used in Excel, as it enables logical comparisons between a result and an expected value.

The syntax of the function is

=IF (logical_test, [value_if_true], [value_if_false])

The arguments of the function are-

logical_test – A value or logical expression that can be evaluated as TRUE or FALSE.

value_if_true – [optional] The value to return when logical_test evaluates to TRUE.

value_if_false – [optional] The value to return when logical_test evaluates to FALSE.

If you wish to get the lowest visits of the platform Mobile inside the range of F5:F15, you can use the following MIN & IF functions-based formula:

=MIN(IF(E5:E15=I5,F5:F15))

Here, E5:E15 is the range for platforms, I5 is a platform namely Mobile, and F5:F15 is the range for the number of visits.

Note. It is an array function and that you need to use CTRL+SHIFT+ENTER instead of just ENTER

Using MIN & IF Functions with Single Criteria

2. Using MIN & IF Functions with Multiple Criteria

Furthermore, if you want to apply the combination of MIN and IF functions based on multiple criteria like finding the lowest number of visits considering site category and mode of platforms.

In that situation, our formula takes the following formula.

=MIN(IF(C5:C15=I5,IF(E5:E15=I6,F5:F15)))

In this formula, C5:C15 is the range for the category of the sites, I5 is a category i.e. Tourism, E5:E15 is the range for platforms, I6 is a mode of platforms i.e. Web, and F5:F15 is the range for the number of visits.

Using MIN & IF Functions with Multiple Criteria

3. Using SMALL IF Function with Single Criteria

If you are interested in applying OR, AND logic in the dataset to find the lowest value, you have to use the SMALL function associated with the IF functions.

The syntax of the combined SMALL IF functions for finding the lowest value is the following.

{=SMALL(IF(criteria_range=criteria, values), n)}

The arguments are-

criteria_range-The cell range where the criteria are available

criteria – The criteria is that you want to apply

values – The cell range of the values

n- n(N) is 1st, 2nd, etc. lowest value.

Firstly, let’s apply the SMALL IF formula with single criteria.

=SMALL(IF(E5:E15=I5,F5:F15),I6)

Here, E5:E15 is the range for platforms, I5 is a mode of platforms i.e. Web, F5:F15 is the range for the number of visits, and N is 1 for the lowest value.

Note. As we are determining the lowest value, the value of n(N) will be 1 always

Using SMALL IF Function with Single Criteria

4. Using SMALL IF Function with Multiple Criteria (OR Logic)

Assuming that you need to find the lowest number of visits considering the mode of platforms e.g. either the lowest visits of Web or Mobile platform.

In this situation, you have to use OR logic where the criteria will be added. For a better understanding look at the following formula.

=SMALL(IF((E5:E15=I5) + (E5:E15=I6),  F5:F15), I7)

Here, E5:E15 is the range for platforms, I5 is a mode of platforms i.e. Web, I6 is another mode of platform i.e. Mobile, F5:F15 is the range for the number of visits, and I7 is for 1, the value of N.

SMALL IF (OR Logic)

5. Using SMALL IF Function with Multiple Criteria (AND Logic)

Let’s imagine, you wish to consider multiple criteria from multiple columns, for example finding the lowest visits considering the name of sites and mode of platforms through using AND logic, you have to multiply the criteria.

In that condition, the formula will be like the following.

=SMALL(IF((B5:B15=I5) * (E5:E15=I6), F5:F15), I7)

Here, B5:B15 is the range for the name of sites, I5 is a site name i.e site 1, E5:E15 is the range for platforms, I6 is a mode of platforms i.e. Web, F5:F15 is the range for the number of visits, and I7 is for 1, the value of N.

Using SMALL IF Function with Multiple Criteria (AND Logic)

6. Using INDEX, MATCH & MIN Functions

The INDEX function in Excel returns the value that is located at a specified place in a range or array. The syntax of the function is

=INDEX (array, row_num, [col_num], [area_num])

The arguments are-

array – A range of cells, or an array constant.

row_num – The row position in the reference or array.

col_num – [optional] The column position in the reference or array.

area_num – [optional] The range in reference that should be used.

The MATCH function is used for locating the search value location in a row, column or table. MATCH is often coupled with the INDEX function to retrieve a corresponding value.

The syntax of the function is

=INDEX (array, row_num, [col_num], [area_num])

The arguments are-

lookup_value – The value to match in lookup_array.

lookup_array – A range of cells or an array reference.

If we combine the INDEX, MATCH, and MIN functions to identify the lowest number of visits, the formula will be like the following.

=INDEX(B5:F15, MATCH(MIN(F5:F15), F5:F15, 0 ), 5 )

Here, B5:B15 is the range for the name of sites, F5:F15 is the range for the number of visits, 5 for the column position of the number of visits.

Using INDEX, MATCH & MIN Functions

7. Using AGGREGATE Function

You may utilize the AGGREGATE function to find the lowest visits considering the multiple criteria.

The function restores an aggregate calculation like finding maximum, minimum, average values, etc.

Now, if you wish to find the lowest number visits in the case of Sport category and Mobile platform through using the function, the formula will be-

=AGGREGATE(15,3,1/((C5:C15=I5)*(E5:E15=I6))*F5:F15,1)

Here, 15 is for the Small option among 19 options as we are finding the lowest value, 3 is for ignoring hidden rows, error values, etc., C5:C15 is for the category of the sites, I5 is a category i.e. Sport, E5:E15 is for the mode of platforms, I6 is a mode of platform i.e. Mobile, F5:F15 is for the number of visits, 1 is the value of n(N).

Do you have any confusion about how you will select options 15 & 3?

Don’t be concerned. The options will be seen automatically when you select the function like the following screenshot.

AGGREGATE options

After entering the formula, you’ll get the following-

Using AGGREGATE Function

8. MINIFS Function with Single Criteria

If you guys need any updated version of a function to find the lowest values with less complexity, then the MINIFS function will be the best.

It is also mentioned that the MINIFS function is supported by Microsoft 365 & Excel 2019. If you don’t have any versions, don’t need to be worried.

Just use the web versions of Microsoft 365. For that, you have to need only a Microsoft account.

The function delivers the least numeric number in a range of values that matches one or more requirements. MINIFS accepts the date, number, text, and other criteria.

The syntax of the function is

=MINIFS (min_range, range1, criteria1, [range2], [criteria2],...

The arguments are-

min_range – Range of values used to determine the minimum.

range1 – The first range to evaluate.

criteria1 – The criteria to use on range1.

range2 – [optional] The second range to evaluate.

criteria2 – [optional] The criteria to use on range2.

Let’s apply the function in our dataset with single criteria like finding the lowest visits for the Mobile platform.

The formula will be-

=MINIFS(F5:F15,E5:E15,I5)

Here, F5:F15 is the cell range for the number of visits, E5:E15 is for the mode of platforms, and I5 is for the platform Mobile.

MINIFS Function with Single Criteria

9. MINIFS Function with Multiple Criteria

Now let’s apply the MINIFS function for multiple criteria like finding the lowest number of visits based on the Tourism category and the Web platform.

The formula will take the following form.

=MINIFS(F5:F15,C5:C15,I5,E5:E15,I6)

Here, F5:F15 is the cell range for the number of visits, C5:C15 is for the category, I5 is for the Tourism category, E5:E15 is for the mode of platforms, and I6 is for the platform namely Web.

MINIFS Function with Multiple Criteria

Things to Keep in Mind

  • Be careful while inputting the formula with proper parentheses and logical operators.
  • Also be cautious about the file name, file location, and Excel extension name.

Conclusions

This is how you can easily find the lowest value through utilizing the functions. If you have any further confusion or suggestions, please let me know below in the comments section.

Thanks for visiting exceldemy.com, a valuable source of Excel problems-solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo