Function Problems

How to Use an Excel Formula to Find the Minimum Value Excluding an Item in WPS Spreadsheets

Steven ParkerSteven Parker Nov 13, 2026 870 views

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.

How to Use an Excel Formula to Find the Minimum Value Excluding an Item in WPS Spreadsheets
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.
Before you start

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.

Solution 1Recommended

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.

1
Select cell F4

Click the cell where you want the minimum MBS result, for example F4.

2
Enter the MINIFS formula

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.

3
Select cell E4

Click the cell where the corresponding product should appear, for example E4.

4
Enter the INDEX/MATCH formula

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.

5
Copy the formulas down

Drag the fill handle or copy the formulas down for additional SA/product rows in the results table.

Use MINIFS and INDEX/MATCH to Exclude the Current Product and Return the Minimum Product
Result: The formula returns the next product with the minimum MBS for the same SA, ignoring the product already selected.

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. 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. 2. Select the result cells: Click F4 for the minimum MBS result and E4 for the corresponding product.
  3. 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. 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. 5. Copy formulas down the results table: Drag the fill handle down to apply the formula pair to the remaining rows.
Returns the minimum MBS product for the same SA while excluding the already selected productUses native MINIFS and INDEX/MATCH formulas without VBAOpens and saves Microsoft Excel .xlsx workbooks with full formula compatibilityFree, lightweight spreadsheet app with a familiar ribbon interface
microsoft office alternative - wps office

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.