How to Find Lowest Value with Criteria in Excel (7 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

Finding the lowest value may be an essential task in some cases. We can easily do those tasks with the help of Microsoft Excel. Have you tried this basic calculation? In this article, I’ll show nine effective ways to find lowest value with criteria in Excel including real-life examples, and of course, proper explanations of the formula for finding the lowest value with criteria. So that you can adjust them according to your requirements.


How to Find Lowest Value with Criteria in Excel: 7 Effective Ways

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.

excel find lowest value with criteria

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


1. Combine Excel MIN & IF Functions to Get Lowest Value

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.  Again, the IF function is frequently used in Excel, as it enables logical comparisons between a result and an expected value.


1.1. Single Criteria

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.

STEPS:

  • Firstly, select the cell where you want to put the formula and enter the formula into that selected cell.

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

  • Then, press the Enter key from your keyboard.

excel find lowest value with criteria

🔎 How Does the Formula Work?

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

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

Read More: How to Use Combined MIN and IF Function in Excel


1.2. 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.

STEPS:

  • Likewise, the previous method selects a cell to see the result in that particular cell.
  • Further, in the combination of the MIN and IF functions formula, our formula takes the following.

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

  • Finally, press Enter.

excel find lowest value with criteria

🔎 How Does the Formula Work?

In this formula, C5:C15 is the range for the category of the sites, I4 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.

Read more: How to Find Minimum Value in Excel


2. Merge SMALL & IF Functions to Find Minimum Value

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.


2.1. Single Criteria

Let’s follow the following instructions to find the lowest value for single criteria.

STEPS:

  • Firstly, let’s apply the combination of SMALL & IF functions formula with single criteria.

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

  • Press the Enter key on your keyboard once more.

🔎 How Does the Formula Work?

Here, E5:E15 is the range for platforms, I4 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.

2.2. Multiple Criteria with 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.

STEPS:

  • In the first place, choose the cell that will be the recipient of the formula, then type the formula into that cell.

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

  • Further, hit the Enter key.

🔎 How Does the Formula Work?

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


2.3. Multiple Criteria with 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.

STEPS:

  • Similar to the previous procedure, choose a cell to view the results there. In that condition, the formula will be like the following.

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

  • Hit the Enter key to see the outcome.

excel find lowest value with criteria

🔎 How Does the Formula Work?

Here, B5:B15 is the range for the name of sites, I4 is a site name i.e site 1, 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 I6 is for 1, the value of N.


3. Integrate INDEX, MATCH & MIN Functions in Excel

The INDEX function in Excel returns the value that is located at a specified place in a range or array. 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. If we combine the INDEX, MATCH, and MIN functions to identify the lowest number of visits, the formula will be like the following.

STEPS:

  • To begin with, choose the cell where you wish to input the formula and type it there.

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

  • To view the result, press the Enter key.

excel find lowest value with criteria

🔎 How Does the Formula Work?

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.


4. Combine INDEX, MATCH & SMALL Functions to Discover Lowest Value with Criteria

We can also use the combination of INDEX, MATCH, and SMALL functions to get the lowest value as well as the lowest item. Let’s follow the procedures to do this.

STEPS:

  • In order to input a formula, first you must choose the cell that will hold the formula and then enter it. With the formula, you will get the lowest category.

=INDEX($C$5:$C$15, MATCH(SMALL($F$5:$F$15, I4), $F$5:$F$15, 0))

  • Press Enter key from the keyboard.

  • Further, to get the lowest value use the following formula.

=INDEX($F$5:$F$15, MATCH(SMALL($F$5:$F$15, I4), $F$5:$F$15, 0))

  • Hit the Enter key.

🔎 How Does the Formula Work?

The formula we use, firstly searches for the smallest value. Then, match it with the category. Finally, show the result.

Read More: How to Find Lowest 3 Values in Excel


5. Use Excel AGGREGATE Function to Compute Lowest Value with Criteria

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, and average values, etc. Now, if you wish to find the lowest number of visits in the case of the Sport category and Mobile platform by using the function. Let’s follow the procedures to find the lowest value with criteria in Excel.

STEPS:

  • Firstly, choose the cell where you want to input the formula and type it there.

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

  • Press Enter.

🔎 How Does the Formula Work?

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, I4 is a category i.e. Sport, E5:E15 is for the mode of platforms, I5 is a mode of platform i.e. Mobile, F5:F15 is for the number of visits, 1 is the value of n (N).

Note: While you will need to select options 15 & 3, select as per your requirements.

Here, we select 15 as we want to get the smallest value.

Also, we select 3, this ignores hidden rows, error values, and nested SUBTOTAL, and AGGREGATE functions.


6. Employ MINIFS Function to Get Lowest Value in Excel

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.


6.1. Single Criteria

Let’s apply the function in our dataset with single criteria to find the lowest value with criteria in Excel for the Mobile platform.

STEPS:

  • Select a cell to display the results in that specific cell, just like in a previous way.

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

  • Hit the Enter key.

🔎 How Does the Formula Work?

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


6.2. 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.

STEPS:

  • Similar to the previous procedure, choose a cell to view the results there.

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

  • Press the Enter button.

🔎 How Does the Formula Work?

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

Read More: How to Find Minimum Value Based on Multiple Criteria in Excel


7. Join VLOOKUP & MIN Functions for Finding Lowest Value

In Excel, we use the VLOOKUP function If we want to search by row in a table or a range. And the MIN function the least number among a group of values is returned. Let’s combine the functions to get the lowest value with the criteria. With the formula, we will get the category that has the lowest value.

STEPS:

  • Firstly, select the cell where you want to put the formula and enter the formula into that selected cell.

=VLOOKUP(MIN($C$5:$C$15),$C$5:$D$15, 2, 0)

  • Further, press the Enter key from your keyboard.

🔎 How Does the Formula Work?

MIN locates the lowest value among a range of values. Then, use a range or a row in a table to guide your VLOOKUP search.

Read More: How to Find Minimum Value with VLOOKUP in Excel


How to Find Highest Value with Criteria in Excel

Excel’s statistical features include the MAX Function. The biggest value from a set of parameters is what MAX returns. We can combine the MAX and IF functions to get the highest value with criteria.

STEPS:

  • Firstly, select the cell and put the formula into that cell.

=MAX(IF(E5:E15=I4,F5:F15))

  • Press the Enter key on your keyboard once more.


Things to Keep in Mind

  • 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.
  • 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.

Download Practice Workbook


Conclusions

This is how you can easily find the lowest value with criteria in Excel. 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 problem solutions.


Related Articles


<< Go Back to Excel MIN Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo