IFERROR Function Introduction / IFERROR Function Use in Excel :-
The use of the IFERROR Function works to identify the error present in the cell and change it, that is, the use of the IFERROR Function works to change the cell containing the error to Blank, another value, and custom message.
IFERROR Function is used to fix which errors.
#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!.
IFERROR Syntax :
IFERROR(value, value_if_error)
Note : IFERROR is not used to replace the 0 present in any cell, it is used to correct the error.
Examples :
In the given excel sheet, 0 and other numbers have been given in the B column but we have to take blank in place of E column 0 and keep all the same as column B.
IF Function Syntax :- =IF (logical_test, [value_if_true], [value_if_false])
We have written the formula present in E Column in F Column. which you can check.
Arguments :-
logical_test - A value or logical expression that can be evaluated as TRUE or FALSE.
value_if_true - [optional] The value to return when logical_test evaluates to TRUE.
value_if_false - [optional] The value to return when logical_test evaluates to FALSE.
IFERROR Function Conditions :-
1. Excel IFERROR , then 0 :-
1. If in the given excel sheet we have to change it to 0 when there is an error inside the cell, then we can do it with the help of many formulas but we will use IFERROR Function for now.
2. In the above image table_array is given inside F,G,H and I column. The number of stock is to be written by looking at the sku code present in the A column from the table_array.
3. For this we will first use vlookup function but in case some sku code in A column is not present in table_array then in that case error appears in B column.
4. To remove the error, we use iferror inside the C column. For this we look at the B3 cell because an error appears in it. To write 0 instead of error, we use Formula =IFERROR(B3,0).
5. If we want to put this error in the first column ie B column itself, then we use the formula =iferror(vlookup(B"cell address",F:G,2,0),0). In this, the cell address i.e. the cell address of the lookup_value has to be written.
2. Excel IFERROR , then Blank :-
1. If in the given excel sheet we have to change it to Blank when there is an error inside the cell, then we can do it with the help of many formulas but we will use IFERROR Function for now.
2. In the above image table_array is given inside F,G,H and I column. The number of stock is to be written by looking at the sku code present in the A column from the table_array.
3. For this we will first use vlookup function but in case some sku code in A column is not present in table_array then in that case error appears in B column.
4. To remove the error, we use iferror inside the C column. For this we look at the B3 cell because an error appears in it. To write Blank instead of error, we use Formula =IFERROR(B3,"").
5. If we want to put this error in the first column ie B column itself, then we use the formula =iferror(vlookup(B"cell address",F:G,2,0),""). In this, the cell address i.e. the cell address of the lookup_value has to be written.
3. Excel IFERROR , then "Not Found" :-
1. If in the given excel sheet we have to change it to "Not Found" when there is an error inside the cell, then we can do it with the help of many formulas but we will use IFERROR Function for now.
2. In the above image table_array is given inside F,G,H and I column. The number of stock is to be written by looking at the sku code present in the A column from the table_array.
3. For this we will first use vlookup function but in case some sku code in A column is not present in table_array then in that case error appears in B column.
4. To remove the error, we use iferror inside the C column. For this we look at the B3 cell because an error appears in it. To write "Not Found" instead of error, we use Formula =IFERROR(B3,"Not Found").
5. If we want to put this error in the first column ie B column itself, then we use the formula =iferror(vlookup(B"cell address",F:G,2,0),"Not Found"). In this, the cell address i.e. the cell address of the lookup_value has to be written.
4. Excel IFERROR , then Custom Value :-
1. If in the given excel sheet we have to change it to Custom VAlue (50) when there is an error inside the cell, then we can do it with the help of many formulas but we will use IFERROR Function for now.
2. In the above image table_array is given inside F,G,H and I column. The number of stock is to be written by looking at the sku code present in the A column from the table_array.
3. For this we will first use vlookup function but in case some sku code in A column is not present in table_array then in that case error appears in B column.
4. To remove the error, we use iferror inside the C column. For this we look at the B3 cell because an error appears in it. To write Custom VAlue (50) instead of error, we use Formula =IFERROR(B3,50).
5. If we want to put this error in the first column ie B column itself, then we use the formula =iferror(vlookup(B"cell address",F:G,2,0),50). In this, the cell address i.e. the cell address of the lookup_value has to be written.
5. Instead of IFERROR, IF and ISERROR can be used together./ (Vlookup + IFERROR vs Vlookup + IF + ISERROR) :-
(i) Vlookup + IF + ISERROR :-
1. If there is an error inside the cell in the given excel sheet, then we have to convert it to blank, then we can do it with the help of many formulas but we will use IF and ISERROR Function for now.
2. In the above image table_array is given inside F,G,H and I column. The number of stock is to be written by looking at the sku code present in the A column from the table_array.
3. For this we will first use vlookup function but in case some sku code in A column is not present in table_array then in that case error appears in B column.
4. To remove the error, we use IF and ISERROR inside the C column. For this we look at the B3 cell because an error appears in it. To write "Not Found" in place of error, we use Formula =IF(ISERROR(VLOOKUP(A6,F:G,2,0)),"Not Found",0).
5. In this, instead of error, "Not Found" comes, but in which there is no error, 0 comes.
But we have to bring the value present in table_array by not bringing 0, then we have to put vlookup again in place of 0, so for this the formula =IF(ISERROR(VLOOKUP(B3,F:G,2,0)),"Not Found",VLOOKUP( B3,F:G,2,0)).
(ii) Vlookup + IFERROR :-
1. If in the given excel sheet we have to change it to "Not Found" when there is an error inside the cell, then we can do it with the help of many formulas but we will use IFERROR Function for now.
2. In the above image table_array is given inside F,G,H and I column. The number of stock is to be written by looking at the sku code present in the A column from the table_array.
3. For this we will first use vlookup function but in case some sku code in A column is not present in table_array then in that case error appears in B column.
4. To remove the error, we use iferror inside the C column. For this we look at the B3 cell because an error appears in it. To write "Not Found" instead of error, we use Formula =IFERROR(B3,"Not Found").
5. If we want to put this error in the first column ie B column itself, then we use the formula =iferror(vlookup(B"cell address",F:G,2,0),"Not Found"). In this, the cell address i.e. the cell address of the lookup_value has to be written.
6. But we have to bring the value present in table_array by not bringing 0, then we have to put vlookup again in place of 0, so for this the formula =IF(ISERROR(VLOOKUP(B3,F:G,2,0)),"Not Found",VLOOKUP( B3,F:G,2,0)) .
0 Comments