How to Replace #N/A with a Custom Message in Excel Lookups
Question details
The user wants to display a custom text message, such as 'Not Found', instead of the standard #N/A error when a lookup formula fails to find a match.

- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Using SEARCH and LOOKUP functions to find data, but encountering unappealing #N/A errors when matches do not exist in the source range.
- Observed behavior
- The lookup formula defaults to returning an #N/A error, disrupting the readability of the spreadsheet instead of providing a user-friendly message.
Ensure you have your existing LOOKUP, VLOOKUP, or SEARCH formula ready, and decide on the exact custom text (like "Not Found" or "No Match") you want to display.
Wrap the Lookup Formula in IFERROR
The IFERROR function intercepts formula errors, including #N/A, and allows you to specify a clean, readable text string to display instead.
By wrapping your core lookup formula inside an IFERROR statement, you instruct the spreadsheet to return your custom message whenever the lookup fails to find a matching value. You can also combine this with an IF statement to handle blank input cells gracefully.
Click on the cell containing your current LOOKUP or SEARCH formula that is returning the #N/A error.
Click into the Formula Bar at the top of the spreadsheet window to begin modifying your formula.
Wrap your existing formula inside the IFERROR function. Format it as: =IFERROR(your_original_formula, "Not Found").
To prevent errors when the search cell itself is empty, add an IF statement. Your final formula will look similar to: =IF(A1="","",IFERROR(LOOKUP(1048577,SEARCH($A$2:$A$7,A1),$B$2:$B$7),"Not Found")).
Press Enter to save the formula. Click and drag the fill handle at the bottom-right of the cell to copy this updated formula to other rows in your dataset.

Handle Formula Errors Easily with WPS Spreadsheet
WPS Spreadsheet provides robust support for all standard lookup and error-handling functions, including IFERROR, VLOOKUP, and XLOOKUP. You can build, troubleshoot, and format complex data seamlessly without dealing with ugly formula errors.
- 1. Download and Install WPS Office: Get WPS Office for free from the official website and open the WPS Spreadsheet application.
- 2. Open Your Data File: Open your existing .xlsx or .xls file containing the lookup formulas that need error handling.
- 3. Apply IFERROR Function: Use the formula bar to seamlessly wrap your LOOKUP formulas with IFERROR, exactly as you would in standard spreadsheet software.
- 4. Save Your Work: Save your document in the standard .xlsx format to ensure full compatibility across different platforms and for other users.

Frequently Asked Questions
Can I use IFNA instead of IFERROR?
Yes. While IFERROR catches all formula errors (like #VALUE!, #REF!, or #DIV/0!), IFNA specifically catches only the #N/A error. The syntax is identical: =IFNA(your_formula, "Not Found"). This is useful if you still want to be alerted to other types of calculation errors.
Why is my LOOKUP formula returning #N/A in the first place?
The #N/A error simply means 'Not Available'. It indicates that the formula cannot find what it has been asked to look for. This usually happens if the lookup value does not exist in your source data array, if there are trailing hidden spaces in your cells, or if the data types (like text vs. numbers) do not match.
Does replacing #N/A with a custom text message affect downstream calculations?
Yes, it can. If other formulas are trying to perform mathematical operations (like SUM or AVERAGE) on a cell that now contains text like "Not Found", they will return a #VALUE! error. If you need to perform calculations later, it is better to return a zero using =IFERROR(your_formula, 0) instead of text.




