Formula Errors

How to Replace #N/A with a Custom Message in Excel Lookups

Emma BrownEmma Brown Nov 2, 2026 870 views

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.

How to Replace #N/A with a Custom Message in Excel Lookups
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the Formula Cell

Click on the cell containing your current LOOKUP or SEARCH formula that is returning the #N/A error.

2
Edit the Formula

Click into the Formula Bar at the top of the spreadsheet window to begin modifying your formula.

3
Apply IFERROR

Wrap your existing formula inside the IFERROR function. Format it as: =IFERROR(your_original_formula, "Not Found").

4
Handle Blank Inputs (Optional)

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")).

5
Apply to Column

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.

Wrap the Lookup Formula in IFERROR
Cross-Platform Compatibility: The IFERROR function works seamlessly in both Microsoft Excel and WPS Spreadsheet, ensuring your custom messages will display correctly regardless of the software used.
Advanced Spreadsheet Tool

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. 1. Download and Install WPS Office: Get WPS Office for free from the official website and open the WPS Spreadsheet application.
  2. 2. Open Your Data File: Open your existing .xlsx or .xls file containing the lookup formulas that need error handling.
  3. 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. 4. Save Your Work: Save your document in the standard .xlsx format to ensure full compatibility across different platforms and for other users.
Fully compatible with Microsoft Excel formulas, functions, and file formats (.xlsx)Built-in error checking and formula evaluation toolsLightweight application with fast processing for large datasetsFree to download and use for everyday data management
microsoft office alternative - wps office

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.