Fix WPS Spreadsheets INDEX and MATCH Returning #N/A with Multiple Criteria
Question details
The user needs to correct an INDEX and MATCH formula that returns #N/A when finding the maximum sales count while applying subject and city criteria.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is looking up a teacher name from a table where sales records must match both a subject and a city, then identify the maximum sales count among those filtered records.
- Observed behavior
- The formula returns #N/A because it identifies the maximum sales count without re-applying the subject and city criteria to the row that INDEX and MATCH returns.
Before editing the formula, open the workbook in WPS Spreadsheets and verify that the table name Shamal_D and the columns Subject, city, Sales Count, and Teacher_name match your worksheet exactly. Also confirm that the Sales Count column contains numeric values rather than text.
Use LET, AGGREGATE, and XMATCH to match the maximum sales within criteria
Best for current WPS Spreadsheets versions that support LET and XMATCH; it returns the correct teacher in one formula and shows "No matching data" when no row qualifies.
This approach first builds a criteria array for the current Subject and city. It then calculates the maximum Sales Count only for rows that meet both criteria, and finally uses XMATCH to locate the row where the criteria and maximum sales are all true.
Click the cell where the teacher name should appear and open the formula bar in WPS Spreadsheets.
Type or paste the following formula and adjust the table and column names to match your workbook: =LET(criteria,(Shamal_D[Subject]=[@Subject])*(Shamal_D[city]=[@City]),maxSales,AGGREGATE(14,6,1/criteria*Shamal_D[Sales Count],1),position,XMATCH(1,criteria*(Shamal_D[Sales Count]=maxSales),0),IFERROR(INDEX(Shamal_D[Teacher_name],position),"No matching data"))
Press Enter. If your WPS Spreadsheets version prompts for an array formula, press Ctrl+Shift+Enter instead.
Verify that the cell now returns the correct teacher name for the current Subject and city, or "No matching data" when no row qualifies.
Use a legacy array formula for older WPS Spreadsheets versions
Use this alternative when LET, AGGREGATE, or XMATCH is not available or when you need a classic INDEX, MATCH, and MAX approach.
Fix INDEX and MATCH #N/A in WPS Spreadsheets
Re-create the multi-criteria lookup in WPS Spreadsheets so the formula finds the teacher with the maximum sales count for the current subject and city. Use the modern LET, AGGREGATE, and XMATCH formula or the legacy array formula, and continue opening and saving .xlsx Excel files without losing compatibility.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains the INDEX and MATCH formula returning #N/A.
- 2. Select the formula cell: Click the cell where the teacher name result should appear and open the formula bar.
- 3. Enter the corrected formula: Use the recommended LET, AGGREGATE, and XMATCH formula, or the legacy array formula if your WPS version does not support those functions.
- 4. Confirm the formula mode: Press Enter for the modern formula or Ctrl+Shift+Enter for the legacy array formula, then verify the result updates correctly.
Frequently Asked Questions
Why does my INDEX and MATCH formula return #N/A even though the maximum sales value exists?
The formula may be finding the maximum sales value without re-applying the Subject and city criteria. Add the same criteria to the final MATCH so the result row must match both the maximum Sales Count and the current Subject and city.
Does WPS Spreadsheets support LET, AGGREGATE, and XMATCH?
Recent versions of WPS Spreadsheets support these functions. If your version does not recognize them, use the legacy array formula with MAX, IF, INDEX, and MATCH and confirm it with Ctrl+Shift+Enter.
How can I return a friendly message instead of #N/A when no matching record exists?
Wrap the formula in IFERROR as shown in the examples. When the filtered criteria have no matching row, the formula displays "No matching data" instead of #N/A.
Can I use this method with a regular range instead of an Excel table?
Yes. Replace Shamal_D[Subject], Shamal_D[city], Shamal_D[Sales Count], and Shamal_D[Teacher_name] with absolute ranges such as $B$2:$B$100, and replace [@Subject] and [@City] with the corresponding row cells.
Do I need to press Ctrl+Shift+Enter for the modern formula?
Usually not. The modern formula with LET and XMATCH can be entered with Enter in current WPS Spreadsheets. The legacy MAX IF array formula requires Ctrl+Shift+Enter so it is evaluated as an array formula.
