Function Problems

How to Find the Exact Cell Supplying a MINIFS Result in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to identify the exact cell reference (or references) that provides the result of a MINIFS formula when multiple cells share the same minimum value.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Locating the exact cell address from a dataset that corresponds to a specific condition and minimum value after calculating a result with the MINIFS formula.
Observed behavior: The MINIFS function accurately returns the correct minimum value, but it does not provide the location or cell reference of that specific value within the dataset.

Before you start

Ensure you know the exact column letter containing your target values and the specific criteria text you used in your original MINIFS formula to accurately configure the lookup formulas.

Solution 1Recommended

Use MATCH to Find the First Matching Cell Reference

Ideal if you only need the address of the very first cell that meets both your criteria and the calculated minimum value.

By multiplying two arrays within the MATCH function, you can create a multi-criteria lookup. The formula evaluates to 1 when both conditions (the criteria and the minimum value) are met, successfully returning the relative row position.

Enter the MATCH array formula

In an empty cell, type the formula: ="J"&MATCH(1,(I:I="Lowest 16 L Minimum:")*(J:J=3),0). Replace 'J' with your value column letter, 'I' with your criteria column, and '3' with the result of your MINIFS formula.

Calculate the result

Press Enter to evaluate the formula. The formula concatenates the column letter with the matched row number, returning the exact cell reference (e.g., J42).

Legacy Excel Requirement: If you are using an older version of Excel that does not support dynamic arrays natively, you may need to press Ctrl+Shift+Enter to execute this array formula.

Solution 2

Use FILTER and SEQUENCE to Return All Matching Cell References

Best for datasets where multiple cells might share the exact same minimum value, and you need to identify every single location.

Powerful Spreadsheet Solution

Easily Find Cell References with Formulas in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like FILTER, MATCH, and SEQUENCE, allowing you to easily pinpoint data locations just like in Excel. It is a highly compatible, robust, and free office suite alternative.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your spreadsheet file, and locate the data where you applied your MINIFS function.
2. Select an output cell: Click on an empty cell where you want the matching cell references to be displayed.
3. Enter the lookup formula: Input either the MATCH or FILTER formula provided in the solutions above, ensuring you update the column letters and criteria to match your specific sheet.
4. Press Enter to locate the cells: Hit Enter. WPS Spreadsheet will instantly calculate the array formula and return the exact cell address or a dynamic list of addresses.

100% compatible with Microsoft Excel formulas and file formats (.xlsx)Supports modern dynamic arrays for complex multi-criteria lookupsLightweight, fast execution even with large datasetsFree to download, install, and use

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the MINIFS function return a cell reference natively?

MINIFS is designed strictly to evaluate a range mathematically and return the minimum numeric value based on your given criteria. It does not possess the capability to return the location, address, or metadata of the cell holding that value.

Can I use INDEX and MATCH to get the full absolute cell address?

Yes. Instead of manually concatenating a column letter (like "J"&...), you can wrap an INDEX/MATCH combination inside the CELL function. For example: =CELL("address", INDEX(J:J, MATCH(1, (I:I="Criteria")*(J:J=3), 0))) will return the absolute address, such as $J$42.

What should I do if my FILTER function returns a #CALC! error?

The #CALC! error typically occurs when the FILTER function finds absolutely no matching values. You can gracefully handle this by adding an 'if_empty' argument at the end of your FILTER formula, such as: ="J"&FILTER(..., ..., "No matches found").

Excel & Data Problems