Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

Replace #N/A or zero in VLOOKUP with blank or specified text in Excel

Have you encountered a problem that it returns the #N/A error value or zero when you applying VLOOKUP function in Excel? Here, the Replace 0 or #N/A with Blank or a Specified Value utility of Kutools for Excel can help you to replace the zero or #N/A error with empty or a specified value by creating a formula in Excel. shot vlookup replace na 1

Replace the zero or #N/A error value with empty

Replace the zero or #N/A error value with specified value

Applying this utility by clicking Kutools > Super LOOKUP > Replace 0 or #N/A with Blank or Specified Value. See screenshot:

shot vlookup replace na 2
 shot arrow
 shot vlookup replace na 3

Replace the zero or #N/A error value with empty

To replace the zero or #N/A error values returned by VLOOKUP function with blank, you can do as below steps:

1. Click Kutools > Super LOOKUP > Replace 0 or #N/A with Blank or Specified Value.

2. In the pop-out dialog, please specify the settings as below:

1) In the Lookup values and Output range section, specify the lookup value cells and the output range separately;

2) Check Replace 0 or #N/A error value with empty option;

3) In the Data range section, select the data range including headers or excluding as you need, and in the Key column drop-down list, choose the column that the lookup values are in, then in the Return column drop-down list, choose the column that you want to return the matched value from.
shot vlookup replace na 4

3. Click OK or Apply, then all matched values are returned, if there are no matched values, it returns blank.
shot vlookup replace na 5

Replace the zero or #N/A error with specified value

If you want to replace the zero or #N/A error values returned by VLOOKUP function with a specified value, please do as these:

1. Click Kutools > Super LOOKUP > Replace 0 or #N/A with Blank or Specified Value.

2. In the pop-out dialog, please specify the settings as below:

1) In the Lookup values and Output range section, specify the lookup value cells and the output range separately;

2) Check Replace 0 or #N/A error value with a specified value option, then type the text you want to replace the zero or #N/A error value with in the below textbox;

3) In the Data range section, select the data range including headers or excluding as you need, and in the Key column drop-down list, choose the column that the lookup value in, then in the Return column drop-down list, choose the column that you want to return the matched value.
shot vlookup replace na 6

3. Click OK or Apply, then all matched values are returned, if there are no matched values, it returns the specified text.
shot vlookup replace na 7

Tip:

If you use this utility at the first time, you can click Example button shot vlookup replace na 8 in the Replace 0 or #N/A with Blank or Specified Value dialog to display a sample sheet for understanding how to use this utility.
shot vlookup replace na 9


Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.