Function Problems

How to Return a Blank Value When VLOOKUP Finds No Match

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a way to configure a VLOOKUP formula so that it outputs a blank cell instead of an error (like #N/A) or a zero when no matching record is found in the data table.

Product: Spreadsheets
Device & OS: not provided
Scenario: Looking up data across tables where some search values may not exist in the source dataset, requiring a clean presentation without visible errors.
Observed behavior: By default, the VLOOKUP function returns an #N/A error if a match is not found, which can disrupt downstream calculations and make the spreadsheet look untidy.

Before you start

Ensure your source data table is properly organized and identify the exact lookup value, the table array range, and the column index number you need for your formula.

Solution 1Recommended

Wrap the VLOOKUP Formula in an IFERROR Function

The most efficient way to hide the #N/A error is by nesting your standard VLOOKUP formula inside an IFERROR function.

The IFERROR function evaluates the primary formula (VLOOKUP) and checks for errors. If the formula evaluates normally, it returns the standard result. If it encounters an error like #N/A, it intercepts it and returns a custom value that you specify—in this case, an empty text string.

Select the target cell

Click on the cell where you want to display the lookup result.

Start the IFERROR formula

In the formula bar, type =IFERROR( to initiate the error-handling function.

Enter your VLOOKUP formula

Immediately after the open parenthesis, type your standard VLOOKUP formula. For example: VLOOKUP(A2, Table1!A:B, 2, FALSE).

Add the blank return value

After closing the VLOOKUP parenthesis, type a comma followed by two double quotation marks with nothing in between, and then a closing parenthesis. It should look like this: , "").

Complete and apply the formula

Press Enter. Your complete formula will be =IFERROR(VLOOKUP(A2, Table1!A:B, 2, FALSE), ""). Drag the fill handle down to apply this clean formula to the rest of the column.

Function Tip: Using FALSE as the fourth argument in your VLOOKUP ensures you are searching for an exact match. This prevents the formula from pulling in incorrect data if an approximate match is found instead.

WPS Spreadsheet Solution

Manage Formulas Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced nested formulas like IFERROR and VLOOKUP, allowing you to seamlessly process complex datasets while keeping your data presentation clean and professional.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing your dataset.
2. Access the formula tools: Navigate to the 'Formulas' tab on the top ribbon and click 'Insert Function'.
3. Nest your functions: Use the formula bar to effortlessly type =IFERROR(VLOOKUP(...), "") and customize it to your data ranges.
4. Fill the column: Double-click the small square at the bottom-right of your active cell to instantly apply the blank-returning formula down the entire column.

Fully compatible with Microsoft Excel (.xlsx) formats, ensuring your VLOOKUP formulas work perfectly.Built-in Function Wizard makes constructing nested formulas quick and intuitive.Lightweight performance guarantees smooth scrolling and calculating even with massive datasets.Free to use with a familiar interface, eliminating any steep learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP return a 0 instead of a blank when a match is found?

If VLOOKUP successfully finds the lookup value but the corresponding return cell is completely empty, it defaults to displaying a 0. To force it to return a blank in this scenario as well, you can append an empty string to your formula: =IFERROR(VLOOKUP(...) & "", "").

Can I use IFNA instead of IFERROR?

Yes. If you want to suppress only the #N/A error (which means 'not found') but still want to be alerted to other critical errors like #REF! or #VALUE!, use the IFNA function: =IFNA(VLOOKUP(...), "").

Will this formula work in older versions of Excel or Spreadsheets?

The IFERROR function is fully supported in WPS Office and Excel 2007 or newer. If you are using a very old legacy system, you would need to use a slightly longer combination: =IF(ISERROR(VLOOKUP(...)), "", VLOOKUP(...)).

How can I return a custom text like 'Not Found' instead of a blank?

To return custom text, simply replace the two double quotes at the end of the IFERROR function with your desired text wrapped in quotes. For example: =IFERROR(VLOOKUP(...), "Not Found").

Excel & Data Problems