Formulas to Replace #N/A or Zero in Vlookup with Blank or Specified Text!!
Tags: excelformula, excelformulas
Published: 2020-10-19 11:37:11

---
title: Formulas to Replace #N/A or Zero in Vlookup with Blank or Specified Text!!
published: true
date: 2020-10-17 07:34:34 UTC
tags: ExcelFormula,Excelformulas
canonical_url: https://geekexcel.com/formulas-to-replace-n-a-or-zero-in-vlookup-with-blank-or-specified-text/
---

## Excel Formulas to Replace #N/A or Zero in Vlookup with Blank or Specified Text:

Here we will show the Formulas to Replace #N/A or Zero in Vlookup with Blank or Specified Text in Excel Office 365. Get an official version of **MS Excel** from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)
## Formula  Syntax:

Use the below Formula for Replacing the #N/A or Zero in Vlookup with Blank or Specified Text in Excel Office 365.

**=[IFERROR](https://geekexcel.com/use-iferror-function-in-microsoft-excel-365-easy-tutorial/)([IF](https://geekexcel.com/use-if-function-in-microsoft-excel-365-simple-steps/)([LEN](https://geekexcel.com/how-to-use-len-function-in-microsoft-excel-365/)([INDEX](https://geekexcel.com/index-function-in-microsoft-excel-365-how-to-use/)([Sheet](https://geekexcel.com/how-to-use-excel-sheet-function-in-office-365-with-examples/)1!$B$2:$B$8,[MATCH](https://geekexcel.com/how-to-use-match-function-in-microsoft-excel-365/)(Sheet1!A11,Sheet1!$A$2:$A$8,0)))=0,””,[VLOOKUP](https://geekexcel.com/how-to-use-excel-vlookup-function-in-office-365/)(Sheet1!A11,IF({1,0},Sheet1!$A$2:$A$8,Sheet1!$b$2:$B$8),2,0)),””)**

### Example:

- In the below screenshot, we will enter the input names in **Column A** , and the prices are given in **Column B**.
- After that, enter the given formula in the selected box.
- Finally, it will show the output in the selected column as shown below.

![](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/07/gh16.png)

## Explanations for Syntax:

- **IFERROR** -This function is used to return custom results when a formula generates an error and standard results when no error is detected. Read more on the [**IFERROR Function**](https://geekexcel.com/use-iferror-function-in-microsoft-excel-365-easy-tutorial/).
- **IF** – In Excel, this function will show the result as one value for TRUE and another one for FALSE. Read more on the [**IF Function**](https://geekexcel.com/use-if-function-in-microsoft-excel-365-simple-steps/).
- **LEN** – The main purpose of the [**LEN Function**](https://geekexcel.com/how-to-use-len-function-in-microsoft-excel-365/) is to return a length of a given text string as a number of a character.
- **INDEX** – This function will return the value at a given position or array. For more details on the [**INDEX Function**](https://geekexcel.com/index-function-in-microsoft-excel-365-how-to-use/).
- **SHEET ** – The Excel [**Sheet Function**](https://geekexcel.com/how-to-use-excel-sheet-function-in-office-365-with-examples/) is used to return an index number of a reference sheet,
- **MATCH ** – It will locate the position of a lookup value in a row, column, or table. Read more on the [**MATCH function**](https://geekexcel.com/how-to-use-match-function-in-microsoft-excel-365/).
- **VLOOKUP** – This function helps to lookup data in a range or table by row. Read more on [**VLOOKUP Function**](https://geekexcel.com/how-to-use-excel-vlookup-function-in-office-365/).

## Bottom-Line:

In this short tutorial, you can get the clarification about how to use the **formulas for replacing #N/A or zero in a VLOOKUP with Blank or Specified Text** in Excel Office 365. Kindly, drop your **feedback** in the below comment section.