How to Make Excel Array Formulas Return Blanks Instead of Errors
Question details
The user wants an INDEX and MATCH array formula to display a blank cell rather than returning a #N/A error or zero once all unique items have been extracted.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting a list of unique names from a dataset using an array formula, where the formula is dragged down past the total number of unique items available.
- Observed behavior
- The formula returns a zero (0) or a #N/A error when there are no more unique names left to extract.
Verify that your original INDEX and MATCH array formula successfully extracts the expected unique names before you modify it to suppress errors.
Wrap the Array Formula with IFERROR
Use the IFERROR function to catch any errors produced by the array formula and replace them with an empty string.
By default, an INDEX and MATCH formula will throw a #N/A error when it can no longer find matching values. Wrapping the entire expression inside an IFERROR function tells the spreadsheet to output a clean, blank cell instead.
Click on the first cell in your column that contains the INDEX and MATCH array formula.
Click inside the formula bar and type '=IFERROR(' immediately after the equals sign. At the very end of your formula, type ',"")'. The updated formula should look like: =IFERROR(INDEX(NAMES,MATCH(0,COUNTIF($AD$4:AD4,NAMES),0)),"").
Press Ctrl + Shift + Enter simultaneously on your keyboard to apply the formula. Curly braces {} will automatically appear around it to indicate it is an array formula.
Click and drag the fill handle at the bottom right corner of the cell to copy the new error-handling formula down the rest of the column.
Handle Complex Array Formulas Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports complex array operations, including IFERROR, INDEX, and MATCH. You can cleanly extract unique values and handle data precisely without dealing with unwanted error codes.
- 1. Open your document: Launch WPS Spreadsheet and open the workbook containing your unique values list.
- 2. Edit the formula: Double-click the cell containing the #N/A error to edit its contents directly, or use the formula bar.
- 3. Add the IFERROR wrapper: Type IFERROR( after the equals sign and append ,"") to the end of your INDEX/MATCH statement.
- 4. Confirm the array: Press Ctrl + Shift + Enter to lock in the array formula, then double-click the fill handle to apply it downward.
Frequently Asked Questions
Why does my INDEX and MATCH formula return #N/A?
The #N/A error occurs when the MATCH function cannot find any remaining matching values in the specified array. In the context of extracting unique values, this naturally happens once all unique items have already been listed and the formula keeps searching.
Can I use ISERROR instead of IFERROR to hide errors?
Yes, but IFERROR is much more efficient. If you use ISERROR, you have to repeat the entire calculation: =IF(ISERROR(formula), "", formula). The IFERROR function simplifies this by only requiring you to write the formula once, which also improves spreadsheet calculation speed.
Why is my array formula returning a 0 instead of a blank?
A formula typically returns a 0 when it successfully evaluates a cell that happens to be completely empty within your named range. To fix this, ensure your named range only covers populated cells, or add an additional IF statement to convert zeros to empty strings.
