How to Find a Different Product by Area and Minimum Mass in WPS Spreadsheets
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.

- 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.
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.
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.
Click the cell where you want the alternative product name to appear, for example G2.
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.
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.
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 XLOOKUP and MINIFS for a more compact formula
Use this if your version of WPS Spreadsheets supports XLOOKUP and you prefer one lookup formula instead of INDEX/MATCH.
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. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains the product, maximum surface area, and mass columns.
- 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. Enter the recommended formula: Type the MINIFS/INDEX/MATCH formula or the XLOOKUP version and adjust the ranges to match your data.
- 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. 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.

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.




