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.

**Download Practice Workbook**

**7 Effective Ways to Find Lowest Value with Criteria in Excel**

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.

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.

**🔎**** 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 Find Minimum value in Excel (Formula and Pivot Table)**

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

**🔎**** 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.

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

**🔎**** 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.

**🔎**** 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.

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

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

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

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