Whenever we lookup 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

**to**

*the VLOOKUP function***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.

**Table of Contents**hide

## Download Practice Workbook

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

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**

*VLOOKUP***return**a

**blank**instead of

**0**in Excel. Here, we have meant blank cell with the value

**0**.

**Steps:**

- At 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*VLOOKUP***0**. If it is not, then the secondfunction 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*C5:C10***2**inside the function. The**0**means at the end of this function means the*match type is exact*.

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

**Read More: ****How to Leave Cell Blank If There Is No Data in Excel (5 Ways)**

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

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

**, and**

*LEN***functions to**

*VLOOKUP***return**a

**blank**instead of

**0**or

**NA**.

**Steps:**

- At 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*LEN***0**. So, we have set this in the logical_test criteria. - 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*C5:C10***2**inside the function. The**0**means at the end of this function means the*match type is exact*.

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

**Read More: ****How to Use XLOOKUP to Return Blank Instead of 0**

**Similar Readings**

**How to Remove Zeros in Front of a Number in Excel (6 Easy Ways)****Hide Rows with Zero Values in Excel Using Macro (3 Ways)****How to Hide Chart Series with No Data in Excel (4 Easy Methods)****Hide Zero Values in Excel Pivot Table (3 Easy Methods)**

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

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

**, and**

*ISNUMBER***functions to**

*VLOOKUP***return blank**instead of the “

**” error.**

*#N/A***Steps:**

- At 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 ofwill execute.*the IF function* - 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*C5:C10***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 means the*match type is exact*.

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

**Read More: ****Excel IFERROR Function to Return Blank Instead of 0**

### 4. Combining IFERROR and VLOOKUP Functions

This section will combine the ** IFERROR** and

**functions to**

*VLOOKUP***return**a

**blank**in Excel.

**Steps:**

- At 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**us the**blank**cell as the output.

**Read More: ****How to Exclude Zero Values with Formula in Excel (3 Easy Ways)**

### 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**

*VLOOKUP***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 blank cell with the value

**0**.

**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*” error. If it finds the error, then it will return*#N/A***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*C5:C10***2**inside the function. The**FALSE**at the end of this function means the*match type is exact*.

- Our formula reduces to →
**IF(IFNA(0,0)=0,””,0)****Output: (Blank)**.- The
function returns*IFNA***0**, which means the*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.

## 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. Moreover, you can visit our site

**ExcelDemy**for more Excel-related articles. Thank you for reading, and keep doing well!