How to Use an Excel Formula to Find the Minimum Value Excluding an Item in WPS Spreadsheets
Question details
User needs a formula-based method to find the minimum MBS value for the same SA group while excluding the product already selected in the results table, without using VBA.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a data table that contains products, SA identifiers, and MBS values; for each selected SA they need the next lowest MBS product, excluding the current product.
- Observed behavior
- The current lookup approach may return the already selected product or require VBA; the user wants a non-VBA formula that works in WPS Spreadsheets.
Confirm that the product, SA, and MBS ranges are aligned across the same rows, for example H4:H18, L4:L18, and K4:K18, and that the SA lookup value in C4 matches the SA column exactly, with no extra spaces.
Use MINIFS and INDEX/MATCH to Exclude the Current Product and Return the Minimum Product
Use this non-VBA formula pair in WPS Spreadsheets when you need the minimum MBS for the same SA while avoiding the product already returned in another row.
MINIFS calculates the smallest MBS that shares the SA in C4 and does not match the current product in B4. The INDEX/MATCH formula then maps that minimum MBS back to the corresponding product.
Click the cell where you want the minimum MBS result, for example F4.
Type =MINIFS($K$4:$K$18,$H$4:$H$18,"<>"&B4,$L$4:$L$18,C4) and press Enter. This excludes the product in B4 and matches the SA in C4.
Click the cell where the corresponding product should appear, for example E4.
Type =INDEX($H$4:$H$18,MATCH(1,($L$4:$L$18=C4)*($K$4:$K$18=F4),0)). In older WPS Spreadsheets versions, confirm with Ctrl+Shift+Enter if required.
Drag the fill handle or copy the formulas down for additional SA/product rows in the results table.

Use an Array Formula with MIN and IF for Older Versions Without MINIFS
If MINIFS is not available in your WPS Spreadsheets version, this alternative calculates the same minimum with a MIN and IF array formula and then returns the matching product.
Find the Minimum Value Excluding an Item in WPS Spreadsheets
Use WPS Spreadsheets to apply MINIFS and INDEX/MATCH formulas so you can find the minimum MBS for the same SA while excluding the current product without VBA.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the Excel workbook containing your MBS, SA, and product columns.
- 2. Select the result cells: Click F4 for the minimum MBS result and E4 for the corresponding product.
- 3. Enter the MINIFS formula: In F4, type =MINIFS($K$4:$K$18,$H$4:$H$18,"<>"&B4,$L$4:$L$18,C4) and press Enter.
- 4. Return the product with INDEX/MATCH: In E4, type =INDEX($H$4:$H$18,MATCH(1,($L$4:$L$18=C4)*($K$4:$K$18=F4),0)); use Ctrl+Shift+Enter if prompted.
- 5. Copy formulas down the results table: Drag the fill handle down to apply the formula pair to the remaining rows.

Frequently Asked Questions
Why does MINIFS use "<>"&B4 instead of just <>B4?
In WPS Spreadsheets and Excel, comparison operators must be concatenated with the cell reference. The expression "<>"&B4 tells MINIFS to exclude the value currently in B4.
Do I need to press Ctrl+Shift+Enter for the INDEX/MATCH formula?
In current WPS Spreadsheets versions that support dynamic arrays, Enter may be enough. In older versions, the array multiplication requires Ctrl+Shift+Enter; if the result is incorrect, confirm as an array formula.
What do columns H, K, L, B, and C represent in this example?
H is the product list, K is the MBS value to minimize, L is the SA group, B is the current product to exclude, and C is the SA lookup value.
Can I use this formula if multiple products share the same minimum MBS?
Yes, but INDEX/MATCH returns the first matching product. If duplicate minimum values matter, add a tie-breaking condition or use a helper column.
What should I do if MINIFS is not available?
Use the MIN and IF array alternative and confirm it with Ctrl+Shift+Enter, or update to a current WPS Office version that supports MINIFS.




