Function Problems

How to Use MINIFS and FILTER to Find the Minimum Value Row in Excel

WPS Editor Oct 1, 2026 868 views

Question details

The user needs to retrieve a specific item or row that contains the minimum value while simultaneously meeting multiple distinct criteria, such as matching a specific case and location.

Product: Excel
Device & OS: not provided
Scenario: Looking up and returning a specific record from a large dataset based on the lowest numerical value within a restricted subset defined by multiple conditions.
Observed behavior: Using the MATCH function alone returns the first minimum value found in the entire column, ignoring the specific case and location criteria and causing the formula to return the wrong row.

Before you start

Ensure your dataset is organized into clear columns without merged cells, and identify the specific target criteria (like location or case) you want your formula to reference.

Solution 1Recommended

Combine FILTER and MINIFS Functions

Using FILTER coupled with MINIFS ensures that you evaluate all criteria before extracting the correct row, solving the limitations of a standard MATCH function.

The FILTER function allows you to extract data based on boolean logic (True/False). By multiplying different criteria together, you create an 'AND' logic statement.

When you include a MINIFS calculation as one of these criteria, the formula filters the dataset to only show rows that match your location, match your case, AND equal the minimum value found under those exact same conditions.

Select the destination cell

Click on an empty cell where you want the resulting item or row data to appear.

Start the FILTER function

Type =FILTER( and select the array or column you want to return (for example, A2:A25 for the item names).

Define your standard criteria

Add your first set of conditions using parentheses and multiplication for AND logic. For example: (B2:B25=I2)*(C2:C25=I3) where B is location and C is case.

Integrate the MINIFS condition

Multiply by the minimum value condition by adding *(D2:D25=MINIFS(D:D,B:B,I2,C:C,I3)). This ensures the force value equals the minimum force for that specific location and case.

Execute the formula

Your final formula should look like =FILTER(A2:A25,(B2:B25=I2)*(C2:C25=I3)*(D2:D25=MINIFS(D:D,B:B,I2,C:C,I3))). Press Enter to return the correct item.

Dynamic Spilling: If there is a tie and multiple items share the exact same minimum force under your criteria, the FILTER function will automatically display all matching items.

Advanced Spreadsheet Data Analysis

Easily Filter Complex Data with WPS Spreadsheet

WPS Office perfectly supports advanced dynamic array formulas like FILTER and MINIFS, allowing you to easily look up minimum values based on multiple conditions. It provides a seamless data analysis experience completely free of charge.

1. Open your dataset: Launch WPS Spreadsheet and open your existing .xlsx file containing the data.
2. Input the dynamic formula: Select the target cell and type your combined =FILTER() and MINIFS() formula just as you would in Excel.
3. Get instant results: Press Enter to instantly display the dynamically filtered row matching your minimum criteria.

Fully compatible with Microsoft Excel formulas (.xlsx)Supports dynamic array functions including FILTER, UNIQUE, and SORTEasily handles multi-criteria data lookups without freezingLightweight application that runs smoothly on Windows, Mac, and Linux

Frequently Asked Questions

Why is my MATCH function returning the wrong row?

When using MATCH for a minimum value, it searches the entire selected array linearly. If there are duplicate minimum values or if you have multiple conditions, a standard MATCH cannot filter out the unrelated rows beforehand unless entered as a complex array formula.

What if I use an older version of Excel that doesn't have the FILTER function?

If FILTER is not available, you can use an INDEX and MATCH array formula combined with MINIFS. For example: =INDEX(A2:A25, MATCH(MINIFS(D:D,B:B,I2,C:C,I3), IF((B2:B25=I2)*(C2:C25=I3), D2:D25), 0)). Remember to press Ctrl+Shift+Enter if required by your software version.

Can I return the entire row of data instead of just one column?

Yes. In the FILTER formula, simply change the return array from a single column (like A2:A25) to the entire data range (like A2:D25). The formula will spill all columns of the matching row.

Excel & Data Problems