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.

**Table of Contents**hide

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

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

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

### 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

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

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

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

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

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

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

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

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