Whenever we look up values using the ** VLOOKUP **values, if there is no match, then it will show a “#N/A” error, and when there are 0 values, it will show the value zero. Are you looking for ways to use

*the*to return a blank instead of 0 or NA in Excel? Then this is the right article for you. In this write-up, we will show five formulas to achieve just that.

**VLOOKUP**function**Table of Contents**hide

## VLOOKUP to Return Blank Instead of 0 or NA: 5 Handy Ways

We have taken a dataset with 2 columns: “Employee” and “Height(cm)” to demonstrate our methods. Moreover, there is another dataset to show the output. We can see that the value “Ross” is not listed on the primary dataset. Therefore, when we try to use **the VLOOKUP function** for that value, we get the “

**N/A**” error in cell

**. However, we have modified the formula to show blank values instead of that error in cell**

*C14***.**

*C16*We have shown 5 formulas to return blank with the ** VLOOKUP** function.

- The first two methods show blank instead of 0.
- Then, method 3,4 shows a blank instead of the “#N/A” error.
- Lastly, method 5 returns blank for both the “#N/A” error and the 0 value.

### 1. Combining IF and VLOOKUP Functions to Return Blank

This section will combine the ** IF** and

**functions to return a blank instead of 0 in Excel. Here, we have meant a blank cell with the value 0.**

*VLOOKUP***Steps:**

- First, type the following formula in cell
.*C13*

`=IF(VLOOKUP(B13,$B$5:$C$10,2,0)=0,"",VLOOKUP(B13,$B$5:$C$10,2,0))`

If we did not add the ** IF** function, then this function would have returned zero.

- Next, press
.*ENTER*

**Formula Breakdown**

- Firstly, this formula has two identical
functions. The first one has a condition attached to it that checks if it equals 0. If it is not, then the second*VLOOKUP*function executes.*VLOOKUP* **VLOOKUP(B13,$B$5:$C$10,2,0)****Output: 0**.- This function looks for the value from cell
in the range of*B13*. If there is a match, then it returns the value from the respective*B5:C10*range as indicated by 2 inside the function. The 0 means at the end of this function the*C5:C10**match type is exact*.

- Our formula reduces to →
**IF(0=0,””,0)****Output: (Blank)**.- Here the
*logical_test*is true, so we have the blank output.

### 2. Incorporating IF, LEN, and VLOOKUP Functions to Return Blank

This second method will incorporate the** IF**,

**, and**

*LEN***functions to return a blank instead of 0 or NA.**

*VLOOKUP***Steps:**

- First, type the following formula in cell
.*C13*

`=IF(LEN(VLOOKUP(B13,$B$5:$C$10,2,0))=0,"",VLOOKUP(B13,$B$5:$C$10,2,0))`

- Next, press
.*ENTER*

**Formula Breakdown**

- Again, this formula has two
functions. Moreover, we have used the first*VLOOKUP*function inside a*VLOOKUP*function, which returns the length of a string. Now, the length of a blank cell is 0. So, we have set this in the logical_test criteria.*LEN* - Now,
**VLOOKUP(B13,$B$5:$C$10,2,0)****Output: 0**.- This function looks for the value from cell
in the range of*B13*. If there is a match, then it returns the value from the respective*B5:C10*range as indicated by 2 inside the function. The 0 means at the end of this function the*C5:C10**match type is exact*.

- Our formula reduces to →
**IF(LEN(0)=0,””,0)****Output: (Blank)**.- The
function returns 0. So, the first portion of the*LEN*function executes and we get the blank cell as the output.*IF*

### 3. Merging IF, ISNUMBER, and VLOOKUP Functions to Return Blank

In the third method, we will use the ** IF**,

**, and**

*ISNUMBER***functions to return a blank instead of the “**

*VLOOKUP**#N/A*” error.

**Steps:**

- First, type the following formula in cell
.*C13*

`=IF(ISNUMBER(VLOOKUP(B13,$B$5:$C$10,2,0)),VLOOKUP(B13,$B$5:$C$10,2,0),"")`

- Next, press
.*ENTER*

**Formula Breakdown**

- This formula has two
functions. Moreover, we have used the first*VLOOKUP*function inside an*VLOOKUP*function, which returns true for a number and false for a non-numerical output. Now, if the first*ISNUMBER*function returns an error, then it will not be a number. So, we have set this in the*VLOOKUP**logical_test**criteria*, and when that happens the false portion of*t**he**IF**function*will execute. - Now,
**VLOOKUP(B13,$B$5:$C$10,2,0)****Output: #N/A**.- This function looks for the value from cell
in the range of*B13*. If there is a match, then it returns the value from the respective*B5:C10*range as indicated by 2 inside the function. Moreover, the value “Ross” cannot be found in the specified cell range, hence it has shown the error. The 0 means at the end of this function the*C5:C10**match type is exact*.

- Our formula reduces to →
**IF(ISNUMBER(#N/A),#N/A,””)****Output: (Blank)**.*Th**e**ISNUMBER**function*returns 0, which means false. So, the second portion of*the**IF**function*executes and we get the blank cell as the output.

### 4. Combining IFERROR and VLOOKUP Functions

This section will combine the ** IFERROR** and

**functions to**

*VLOOKUP***return**a

**blank**in Excel.

**Steps:**

- First, type the following formula in cell
.*C13*

`=IFERROR(VLOOKUP(B13,$B$5:$C$10,2,FALSE),"")`

- Next, press
.*ENTER*

**Formula Breakdown**

- This formula has a single
function, and we have used this inside an*VLOOKUP*function, which returns modified output in the case of an error. Now, the modified output is set to a blank cell.*IFERROR* - Now,
**VLOOKUP(B13,$B$5:$C$10,2,FALSE)****Output: 0**.- This function looks for the value from cell
in the range of*B13*. If there is a match, then it returns the value from the respective*B5:C10*range as indicated by*C5:C10***2**inside the function. The**FALSE**at the end of this function means the*match type is exact*.

- Our formula reduces to →
**IFERROR(#N/A,””)****Output: (Blank)**.- This function modifies any errors and returns the blank cell as the output.

### 5. Using Combined Functions to Return Blank Instead of 0 or #N/A! Error

In the final method, we will combine the ** IF**,

**, and**

*IFNA***functions to create a formula to return blank instead of 0 or NA. Up to this point, every method was specific to a single value. However, this single formula will work for both conditions. Here, we have meant a blank cell with the value 0.**

*VLOOKUP***Steps:**

- At first, type the following formula in cell
.*C13*

`=IF(IFNA(VLOOKUP(B13,$B$5:$C$10,2,FALSE),0)=0,"",VLOOKUP(B13,$B$5:$C$10,2,FALSE))`

- Next, press
.*ENTER*

**Formula Breakdown**

- Again, this formula has two
functions. Moreover, we have used the first*VLOOKUP*function inside an*VLOOKUP*function, which checks for the “*IFNA**#N/A*” error. If it finds the error, then it will return 0. Otherwise, it will return the original output. However, we have set it so that when it finds 0, it will return a blank cell. - Now,
**VLOOKUP(B13,$B$5:$C$10,2,FALSE)****Output: 0**.- This function looks for the value from cell
in the range of*B13*. If there is a match, then it returns the value from the respective*B5:C10*range as indicated by 2 inside the function. The FALSE at the end of this function means the*C5:C10**match type is exact*.

- Our formula reduces to →
**IF(IFNA(0,0)=0,””,0)****Output: (Blank)**.- The
function returns 0, which means the*IFNA**logical_test*is true. So, the first portion of thefunction executes and we get the blank cell as the output.*IF*

## Practice Section

We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.

**Download Practice Workbook**

## Conclusion

We have shown you five formulas to use ** VLOOKUP** to return blank instead of 0 or NA in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Thank you for reading, and keep doing well!

## Related Articles

- 10 Best Practices with VLOOKUP in Excel
- 7 Practical Examples of VLOOKUP Function in Excel
- VLOOKUP Example Between Two Sheets in Excel
- How to Use Dynamic VLOOKUP in Excel
- Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP
- How to Make VLOOKUP Case Sensitive in Excel
- VLOOKUP from Another Sheet in Excel
- How to Use VLOOKUP Formula in Excel with Multiple Sheets
- How to Remove Vlookup Formula in Excel
- How to Hide VLOOKUP Source Data in Excel
- How to Copy VLOOKUP Formula in Excel