Ignore Blank and Unmatched Cells When Averaging INDEX MATCH Results in WPS Spreadsheets
Question details
The user needs to average INDEX MATCH lookup results while excluding blank dropdown selections and unmatched N/A values so #N/A errors do not affect the AVERAGE result.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains dropdown cells that may be blank or set to N/A, and a lookup formula must average only matching numeric values from another sheet.
- Observed behavior
- A traditional AVERAGE INDEX MATCH formula returns #N/A or includes errors when a dropdown value has no match.
Open WPS Spreadsheets and locate the workbook that contains the dropdown selections and the lookup sheet. Check that the lookup range and the return range have the same number of rows before entering the formula.
Wrap INDEX MATCH in IFERROR and average the returned values
Use this solution when dropdown cells may be blank or marked N/A, and you want AVERAGE to ignore rows that have no matching lookup value.
The IFERROR function converts unmatched lookup results into empty strings. Because AVERAGE ignores text values, those rows are omitted from the calculation instead of producing #N/A.
Click the cell where the average should appear.
Type or paste: =AVERAGE(IFERROR(INDEX('Drop Down'!F2:F6,MATCH(C8:C32,'Drop Down'!E2:E6,0)),""))
Replace 'Drop Down'!, F2:F6, E2:E6, and C8:C32 with your actual sheet name and lookup/return ranges.
If WPS Spreadsheets does not spill the result automatically, press Ctrl+Shift+Enter to commit the formula as an array formula.
Fix AVERAGE INDEX MATCH Errors with WPS Spreadsheets
Use WPS Spreadsheets to wrap your INDEX MATCH lookup in IFERROR and return an empty string, so AVERAGE ignores blank dropdown selections and unmatched values instead of returning #N/A.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains your dropdown sheet and lookup ranges.
- 2. Select the average result cell: Click the cell where you want the calculated average to appear.
- 3. Enter the IFERROR AVERAGE formula: Type =AVERAGE(IFERROR(INDEX('Drop Down'!F2:F6,MATCH(C8:C32,'Drop Down'!E2:E6,0)),"")) and adjust the ranges to match your data.
- 4. Commit and verify: Press Enter or Ctrl+Shift+Enter if required, then test with blank and N/A dropdown selections to confirm that #N/A no longer appears.
Frequently Asked Questions
Why does my AVERAGE INDEX MATCH formula show #N/A when a dropdown is blank?
Because MATCH cannot find a blank or N/A value in the lookup range, so INDEX returns a #N/A error and AVERAGE passes it through. Wrap INDEX MATCH in IFERROR and return "" so AVERAGE ignores that row.
Will AVERAGE really ignore the empty string returned by IFERROR?
Yes. AVERAGE ignores text values, including empty strings, so rows with no matching lookup value are excluded instead of being treated as zero or an error.
Can I use this formula in WPS Spreadsheets and Microsoft Excel?
Yes. WPS Spreadsheets supports IFERROR, INDEX, MATCH, and AVERAGE the same way as Excel. In versions that require array entry, press Ctrl+Shift+Enter after typing the formula.
What should I do if my lookup range is on another sheet?
Include the sheet name in single quotes, for example 'Drop Down'!E2:E6. Make sure the sheet name and range match your workbook exactly.
Does the same approach work in Google Sheets?
The IFERROR and AVERAGE logic is the same. In Google Sheets you may need to wrap the formula in ARRAYFORMULA if the range-based MATCH does not expand automatically.
