Formula Errors

How to Hide Excel #N/A Errors with IFERROR in Lookups

Elise Williams Oct 1, 2026 869 views

Question details

The user wants to hide #N/A errors generated by an Excel lookup formula that searches multiple columns, ensuring the cell remains blank when no match is found.

How to Hide Excel #N/A Errors with IFERROR

Product: Excel
Device & OS: not provided
Scenario: Performing a multi-column lookup for a part number where some cells might be blank or lack a matching entry in either lookup column.
Observed behavior: The formula returns an #N/A error instead of a clean, blank cell when both the primary and fallback lookups fail to find a match.

Before you start

Verify your base VLOOKUP or INDEX/MATCH formulas are working correctly for existing data before wrapping them in error-handling functions.

Solution 1Recommended

Use Nested IFERROR Functions for Multiple Lookups

Wrap each fallback lookup in an IFERROR function to catch all #N/A errors and output a clean blank cell.

When searching across multiple columns (like a standard part number and an OEM part number), a single IFERROR is not enough if the fallback lookup also fails. Nesting a second IFERROR ensures both lookup attempts are covered.

Locate the primary formula

Select the cell containing your combined lookup formulas.

Wrap the first lookup

Modify the formula to start with IFERROR. For example: =IFERROR(INDEX(Pricing!F:F, MATCH(B25, Pricing!A:A, 0)), ...)

Wrap the second lookup

In the 'value_if_error' argument of the first IFERROR, place your second lookup formula and wrap it in another IFERROR.

Set the final fallback value

At the end of the second IFERROR, use double quotes ("") to return a blank cell. The final formula should look like: =IFERROR(INDEX(Pricing!F:F,MATCH(B25,Pricing!A:A,0)), IFERROR(INDEX(Pricing!F:F,MATCH(B25,Pricing!B:B,0)), ""))

Apply the formula

Press Enter to save the formula, then drag the fill handle down to apply it to the rest of your column.

Use Nested IFERROR Functions for Multiple Lookups

Custom Error Messages: You can replace the empty string ("") at the end of the formula with custom text like "Part Not Found" if you prefer a descriptive message instead of a blank cell.

Solution 2

Use a Single IFERROR for Basic Lookups

If you are only searching a single column, you only need one IFERROR function to hide the #N/A error.

Efficient Formula Troubleshooting

Fix Formula Errors Easily in WPS Spreadsheet

WPS Office fully supports advanced Excel functions, including nested IFERROR, INDEX, and MATCH. You can handle complex data lookups smoothly, keeping your spreadsheets clean and error-free.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the file containing the #N/A errors.
2. Edit the formula: Click on the cell with the error and go to the formula bar.
3. Apply IFERROR: Type =IFERROR( before your lookup formula, add , "") at the end, and press Enter to hide the error.

100% compatibility with Microsoft Excel formulas and functionsBuilt-in error checking and intuitive function hintsLightweight application that processes large datasets quicklyFree to use for everyday spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDEX/MATCH formula return #N/A?

The #N/A error occurs when the MATCH function cannot find the specified lookup value within the search array. It commonly happens if the source cell is blank, contains extra spaces, or the item truly does not exist in your data list.

Can I use IFNA instead of IFERROR?

Yes. IFNA specifically targets only #N/A errors, whereas IFERROR catches all formula errors (such as #DIV/0!, #REF!, or #VALUE!). If you only want to hide missing lookup results but still want to be alerted to other mathematical or structural errors, IFNA is the better choice.

How do I make the formula return 'Not Found' instead of a blank cell?

In the last argument of your IFERROR or IFNA function, replace the empty double quotes ("") with your desired text enclosed in quotes. For example, use =IFERROR(your_formula, "Not Found").

Excel & Data Problems