Excel is widely used for data management, and practically everyone utilizes it these days. At the same time, it is extremely user-friendly. Excel offers a number of functions and formulae that may make boring tasks much easier. VLOOKUP() is another extremely helpful method that is commonly used to ‘look up’ any data from a large table or a range by row. It allows for approximate or precise matching dependent on whether one of its arguments is ‘True’ or ‘False’. In general, the VLOOKUP() method is used to retrieve data from a table, list, or range. It primarily offers two features: approximate match and exact match.
In this article, we will see how to Return a Blank or a Specific value instead of 0 or N/A in Excel. There are several ways to achieve that. All are discussed below.
Method 1: Using IF and VLOOKUP together
- Returning Blank in Place of 0: Using the IF and VLOOKUP together we can return a Specific Value or Return Blank.
Dataset used in this Tutorial
Syntax of IF function in Excel –
= IF (CONDITION, [VALUE_IF_CONDITION_TRUE], [VALUE_IF_CONDITION_FALSE])
Explanation:
CONDITION – Conditions that yields either True (1) or False (0) values. For example – say we want to check if the value of certain cell is greater than 10, so in condition, we will write it as cell_number > 10.
- [VALUE_IF_CONDITION_TRUE] – Here we will put a value which will be returned if the Condition yields to True.
- [VALUE_IF_CONDITION_FALSE] – Here we will put a value which will be returned if the Condition yields to False.
We will be searching for Desktop’s Price and in place of 0, we will return Blank.
Syntax of VLOOKUP function in Excel –
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Explanation:
- lookup_value – It denotes the value which we are looking for in the first column of our spreadsheet/dataset/table. We have to provide this value otherwise it will not work.
- table_array – It denotes the entire dataset/table/ (from starting cell till the last cell) in which we want to search. This is also necessary to provide.
- col_index_num – It denotes the column number of table_array from which we want to return a value. It is also a required argument.
- [range_lookup] – It denotes either TRUE(1) or FALSE (0), TRUE denotes it will search for closest matches if the Exact match is not found and FALSE denotes it will only search for exact matches. if we put FALSE (0) and didn’t get any exact match then this will return an error.
Combining IF and VLOOKUP Formula
=IF(VLOOKUP(D2,A2:B8,2,0)=0,””,VLOOKUP(D2,A2:B8,2,0))
In this, we are using the IF and VLOOKUP formula together. Here D2 is the lookup value, A2:B8 is the array of the table, 2 is the column number and 0 is the range lookup that denotes false. After that =0,”” means at the place of the zero we return the cell as empty.
Please change the values according to User’s Column Location.
- Returning any Specific Value in place of 0
=IF(VLOOKUP(D2,A2:B8,2,0)=0,”The Value is 0″,VLOOKUP(D2,A2:B8,2,0))
If we want to return the string value then all other VLOOKUP values are same like, D2 is the lookup value, A2:B8 is the array of the table, 2 is the column number and 0 is the range lookup that denotes false. After that =0,”The Value is 0″ means at the place of the zero we return the cell with string as The Value is 0.
Output
The above formula returns a String, if we want to return an Integer value then we can use the below formula
=IF(VLOOKUP(D2,A2:B8,2,0)=0,100,VLOOKUP(D2,A2:B8,2,0))
If we want to return the 100 number then all other VLOOKUP values are same like, D2 is the lookup value, A2:B8 is the array of the table, 2 is the column number and 0 is the range lookup that denotes false. After that =0,100 means at the place of the zero, we return the cell with the number as 100.
Output
Method 2: Using ISNA, IF and VLOOKUP together
ISNA function in excel is used to check if any cell has a ‘#N/A’ error in it, it takes only one parameter and returns either TRUE (N/A error exists) or FALSE (N/A error doesn’t exist).
Syntax of ISNA function
ISNA(parameter)
Explanation:
parameter – This parameter can either be another function that might return ‘#N/A’ error, a cell that has ‘#N/A’ in it, any formula which might yield ‘#N/A’ error, or any value.
As output, it returns either TRUE or FALSE
- TRUE – If the parameter return ‘#N/A’ error.
- FALSE – If the parameter doesn’t return ‘#N/A’ error.
Combining IF, ISNA, and VLOOKUP Formula
- Returning Blank if there is a N/A
=IF(ISNA(VLOOKUP(D2,A2:B8,2,FALSE)),””,VLOOKUP(D2,A2:B8,2,FALSE))
In this, we are using the IF, ISNA, and VLOOKUP formula together. Here D2 is the lookup value, A2:B8 is the array of the table, 2 is the column number and FALSE is the range lookup that denotes 0. After that “” means at the place of the #N/A we return the cell as empty.
Output
Change the values according to User’s Column Location.
- Returning a Specific Value if there is a N/A –
=IF(ISNA(VLOOKUP(D2,A2:B8,2,FALSE)),”There is NA”,VLOOKUP(D2,A2:B8,2,FALSE))
If we want to return the string value then all other VLOOKUP values are the same like, D2 is the lookup value, A2:B8 is the array of the table, 2 is the column number and FALSE is the range lookup that denotes 0. After that “There is N/A” means at the place of the #N/A we return the cell with string as There is N/A.
Output
The above formula returns a String.
Method 3: Using IFERROR and VLOOKUP together
the IFERROR function checks a formula (or a cell) for mistakes and returns a specified value (user-defined) in place of the error. A text message, an empty string, a logical value, a number, etc. can all be included in the returned value. The function deals with errors like “#N/A,” “#DIV/0!,” “#NAME?,” and others.
Syntax of IFERROR
IFERROR(value,value_if_error)
- value – This value needs to be verified for correctness. It could be a value, formula, cell reference, or expression.
- value_if_error – If an error is discovered, this is the output that is returned. It may take the form of a text string, empty cell, number, logical value, etc.
Combining IFERROR and VLOOKUP Formula
- Returning a Specific Value in place of N/A
Using the IFERROR function we can check for #N/A error and return either Blank or a Specific Value.
=IFERROR(VLOOKUP(D2,A2:B8,2,0),”There is NA”)
In this, we are using the IFERROR and VLOOKUP formulas together. Here D2 is the lookup value, A2:B8 is the array of the table, 2 is the column number and 0 is the range lookup that denotes FALSE. After that “There is NA” means at the place of the #N/A we return the string as There is NA.
Output
The above formula returns a String.
- Returning Blank if there is N/A –
=IFERROR(VLOOKUP(D2,A2:B8,2,0),””)
If we don’t want to return the value then all other VLOOKUP values are the same like, D2 is the lookup value, A2:B8 is the array of the table, 2 is the column number and 0 is the range lookup that denotes FALSE. After that “” means at the place of the #N/A we return the cell as empty.
Output