Скачать или смотреть How to Make Excel VLOOKUP Return Blank Instead of 0 or # N/A

Learn how to adjust your Excel VLOOKUP formulas to return a blank cell instead of `0` or `# N/A` when searching for data.
---
This video is based on the question https://stackoverflow.com/q/67597125/ asked by the user 'cjvdg' ( https://stackoverflow.com/u/6729785/ ) and on the answer https://stackoverflow.com/a/67597243/ provided by the user 'norie' ( https://stackoverflow.com/u/2850026/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Excel VLOOKUP return blank instead of 0 or # na

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Mastering VLOOKUP: How to Return Blank Instead of 0 or # N/A

If you've ever worked with Excel, you're probably familiar with the VLOOKUP function, which is often used to search for a specific value in a table and return a corresponding value. However, many users face a common challenge: when the lookup value is blank or doesn’t exist, Excel returns 0 or # N/A instead of leaving the cell blank. This can be confusing and clutter your data analysis. So, is there a way to have a blank cell returned instead? Absolutely! Let’s explore a few solutions.

Understanding the Problem

When you use the VLOOKUP function in Excel:

Entering a lookup value that is blank may return 0.

Attempting to search for a value that doesn’t exist will often result in a # N/A error.

Rather than displaying these undesirable outputs in your workbooks, you may prefer a cleaner look by returning a blank cell when no valid result is found.

Solution with Excel 365

For those on Excel 365, you have a powerful function at your disposal called LET which allows for cleaner and more efficient formulas. Here’s how you can modify your VLOOKUP formula:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Formula:

LET(res,...): This defines the variable res to store the VLOOKUP result.

VLOOKUP($F$18, G1_Q1, 15, 0): This searches for the value found in cell $F$18 within the range G1_Q1, returning the matching value from the 15th column.

ISERROR(res): Checks if the res is an error, like # N/A.

res=0: Checks if the returned value is 0.

The IF function: If either condition is true (an error or 0), it returns an empty string (“”), leaving the cell blank. Otherwise, it returns the value of res.

Solution for Earlier Versions of Excel

If you’re using an earlier version of Excel that doesn’t support the LET function, don’t worry! You can still achieve a similar outcome with the following formula:

[[See Video to Reveal this Text or Code Snippet]]

Key Components Include:

The OR function: It combines the checks for both errors and if the result equals 0.

The IF function: Just like in the previous case, it returns an empty string if either of the checks is true, otherwise it displays the VLOOKUP result.

Alternate Method: Hide Zeroes

If you prefer not to alter your formulas but wish to hide the 0 values, consider applying formatting:

Go to the "Home" tab in the ribbon.

Click on "Format" in the Cells group.

Choose "Format Cells".

Navigate to the "Number" tab and select "Custom".

Enter the format 0;-0;;@ .

This custom format tells Excel to display positive and negative numbers normally, but to leave blanks for 0 values.

Conclusion

With these methods, you can avoid having your Excel VLOOKUP results display 0 or # N/A when it's unnecessary. Instead, your worksheets can look cleaner and more professional by showing a blank cell when there’s no valid data to report. Experiment with these formulas and formatting techniques to find the one that best fits your workflow!