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
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(IF(LEN(INDEX(Sheet1!$B$2:$B$8,MATCH(Sheet1!A11,Sheet1!$A$2:$A$8,0)))=0,””,VLOOKUP(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.
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.
- 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.
- LEN – The main purpose of the LEN Function 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.
- *SHEET * – The Excel Sheet Function 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.
- VLOOKUP – This function helps to lookup data in a range or table by row. Read more on VLOOKUP Function.
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.
Top comments (0)