Function Problems

How to Find a Different Product by Area and Minimum Mass in WPS Spreadsheets

Elise WilliamsElise Williams Nov 13, 2026 869 views

Question details

The user needs a WPS Spreadsheets formula that looks up an alternative product in a table, limits the result to a specified maximum surface area, excludes a product entered in an input cell, and returns the product tied to the smallest mass.

How to Find a Different Product by Area and Minimum Mass in WPS Spreadsheets
Product
WPS Spreadsheets
Device & OS
not provided
Scenario
The user has a product list with product names, maximum surface area, and mass values. They want to select an input product to exclude and have the formula return another product with the required maximum surface area and the minimum mass.
Observed behavior
The expected result is a product name that is different from the input product, meets the maximum surface area criterion, and is associated with the lowest mass.
Before you start

Prepare a small sample table with columns such as Product, Maximum Surface Area, and Mass, and confirm that the product to exclude is in a separate input cell. Use consistent units for area and mass so the minimum and matching criteria do not mix different measurements.

Solution 1Recommended

Use MINIFS with INDEX and MATCH to return the matching product

Best for WPS Spreadsheets versions that support MINIFS and array-style INDEX/MATCH; it explicitly excludes the input product and checks maximum surface area.

This formula first calculates the minimum mass among rows whose maximum surface area equals the target cell and whose product is not the input product. It then locates the row meeting all three conditions and returns the product name.

1
Select the result cell

Click the cell where you want the alternative product name to appear, for example G2.

2
Enter the INDEX/MATCH/MINIFS formula

Type a formula like =INDEX(A2:A100,MATCH(1,(B2:B100=F2)*(A2:A100<>E2)*(C2:C100=MINIFS(C2:C100,B2:B100,F2,A2:A100,"<>"&E2)),0)) and adjust the ranges to your table.

3
Commit the formula

Press Enter in current WPS Spreadsheets. If you are using an older version that treats it as an array formula, press Ctrl+Shift+Enter to confirm it instead.

4
Verify with sample data

Change the input product in E2 and confirm that the returned product changes while still matching the maximum surface area in F2 and showing the lowest mass.

Use MINIFS with INDEX and MATCH to return the matching product
Result: The formula returns the first matching product in row order when several products share the same minimum mass.
WPS Spreadsheets

Find the correct alternative product in WPS Spreadsheets

Use the WPS Spreadsheets formula engine to combine MINIFS with INDEX/MATCH or XLOOKUP, so you can exclude one product, match the required maximum surface area, and return the product with the minimum mass.

  1. 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains the product, maximum surface area, and mass columns.
  2. 2. Locate the input and result cells: Click the input cell for the product to exclude and the target maximum surface area cell, then select an empty result cell.
  3. 3. Enter the recommended formula: Type the MINIFS/INDEX/MATCH formula or the XLOOKUP version and adjust the ranges to match your data.
  4. 4. Test with obvious samples: Try a sample row where the excluded product has the lowest mass to confirm the formula skips it and returns a different product.
  5. 5. Save and reuse: Save the workbook; WPS Spreadsheets keeps it in Excel-compatible .xlsx format so you can continue using the formula in Excel if needed.
Supports Excel-compatible functions such as MINIFS, INDEX/MATCH, and XLOOKUP in .xlsx workbooksOpens, edits, and saves Microsoft Excel files without format conversionFree, lightweight, and easy-to-use spreadsheet interface with familiar formula entryLets you test formulas against sample data in the same workbook before applying them to the full dataset
microsoft office alternative - wps office

Frequently Asked Questions

Will this formula work in WPS Spreadsheets the same way as in Excel?

Yes. Current WPS Spreadsheets versions support MINIFS, INDEX/MATCH, and XLOOKUP, so the same formula logic works in .xlsx files. If your version requires legacy array behavior, press Ctrl+Shift+Enter after typing the formula.

Why does the formula exclude the input product?

The condition A2:A100<>E2 prevents the row containing the product in E2 from being considered. Without this condition, the formula may simply return the input product when it has the lowest mass.

What happens if two products have the same minimum mass?

The formula returns the first matching product in the order the rows appear. You can add a tie-breaking helper column or use a secondary criterion if you need a deterministic result.

Can I use this for more than one product to exclude?

Yes. Add additional AND criteria, for example (A2:A100<>E2)*(A2:A100<>H2), to exclude more than one product from the minimum mass calculation.

Do I need to use maximum surface area as a criterion?

Yes. The minimum mass must be calculated only among rows with the same maximum surface area as the target cell; otherwise the formula can return a product from a different surface area group.