The IF conditioner is widely used in Excel to build formulas and determines that an action is only executed if a specific condition is met. As a general rule, this condition refers to values or a sequence of specific characters. But, much more often than desired, Excel issues error messages in a cell. The reason may be that you have misspelled the formula or that it is missing values for a correct calculation. In these cases, you can create an acceptable alternative with the IFERROR function..
Whenever Excel is unable to correctly execute a calculation or action, the program will generate an error message in the cell. These messages usually start with a pound sign (#).
In most cases, you can try to resolve the underlying problem. But it may also be the case that you already have these types of errors in advance and do not want to solve them. In this case, it is worth making use of Excel's IFERROR function. This function allows you, for example, to replace the error indication with your own message or a value..
This measure not only helps you keep your spreadsheet neater, it also prevents other calculations from being affected . If in another formula you have specified a range of cells where an error message appears, it is possible that this formula generates a new error. To avoid this situation, IFERROR allows you to enter a value that the formula can work with.
Excel also has the ISERROR function. This outputs the values TRUE or FALSE in relation to the occurrence of errors. However, most users integrate it into an SI function. YESERROR saves nesting..
With the IFERROR formula, the spreadsheet makes it easier than ever. You only need to fill in two arguments: Where can an error arise? What should happen if an error occurs?
=SI.ERROR(valor; valor_si_error)
What must be introduced in the arguments?
In Excel, formulas always begin with the equals symbol (=). If you don't put it, the formula will appear as conventional text in the cell. If this is what you want and you even want the equals symbol to be displayed in the cell as well, you should put a single quote in front of the formula (').
The Excel function can be used in various situations, alone or in combination with other functions.
Probably the easiest way to apply IFERROR to working with Excel is to modify the error notification . In this context, let's imagine a simple calculation: the value in cell A3 is multiplied by the value in cell B3. If in one of the two references there is a letter instead of a number, for example due to a misprint, Excel issues the error message #VALUE !. However, if you link the calculation with the IFERROR function, you can change how this fault is displayed.
=SI.ERROR (A3*B3; "valores erróneos")
If you want the cell to remain empty in case of error, enter two quotes in a row.
Now the correct result or your custom error message (in our case? Wrong values?) Will be displayed . If you have multiple calculations of this type, you can apply the formula to all cells.
Instead of replacing the error message, you can also apply IFERROR to another cell and thus create a comment .
=SI.ERROR (C3; "Aquí hay un error")
In this case, the function returns the value of C2 even though there is no error. The reason is that the IFERROR function does not know any alternative value . If you want to avoid this circumstance, the combination of YES and ISERROR seems the best option.
=SI(ESERROR(C2)=VERDADERO; "Aquí hay un error"; "")
In practical use of Excel, IFERROR is especially useful in combination with the VLOOKUP function. This function is known for regularly throwing the # N / A error . The VLOOKUP function helps you to apply a search function directly on your Excel sheet. However, if you make a mistake in the syntax of the formula or a typo in the search term, Excel issues an error message. SI.ERROR allows you to replace the message # N / A, of very little use, with your own message.
=SI.ERROR(BUSCARV("Lung, Fritz";A2:C7;2;0); "Introducción errónea")
Searching for the term? Lung, Fritz? would cause the # N / A error to be displayed, as the name does not appear in the table (since the last argument of VLOOKUP is set to 0, approximate values are not allowed). But, since you've nested the function with IFERROR, your own error message appears instead of the standard message.
If you want to couple multiple VLOOKUP to each other , the IFERROR function is also very useful. Let's imagine you want to search for an object in a table. If the object is not found, you want the following table to be checked and so on. Since VLOOKUP generates an error if it is not possible to find the entered text, IFERROR allows starting a new VLOOKUP.
=SI.ERROR(BUSCARV(100;A3:B7;2;0);SI.ERROR(BUSCARV(100;D3:E7;2;0);SI.ERROR (BUSCARV(100;G3:H7;2;0);"No encontrado")))
Every time VLOOKUP does not find the argument searched for and throws an error, the function jumps to the next table. If the value does not appear in any of the tables and all the nesting functions have already been executed, the message you defined will appear.
Excel also has the SI.ND function. It works like IFERROR, but its activity is limited to the # N / A error message. That is, if you opt for this function instead of the more general function, you will only filter the # N / A error. All other errors will continue to be indicated. This way, you can better react to errors in your formula.
Let's imagine that you have several calculations and you want to sum the results with the SUM function. If you've made a mistake in your calculations and Excel gives you an error message instead of a numeric value, the addition function may not work correctly either . Therefore, ensure your calculations with IFERROR and indicate the value 0 instead of the error. So the function can execute the task and the sum is not altered.
=SI.ERROR(A3*B3; 0)
You also have the option to apply an alternative text only to the SUM function to warn the user of the error.
=SI.ERROR(SUMA(C2:C7); "Valores erróneos")
Using the IFERROR function you can replace Excel error messages with your own values or messages in different situations. You can also use IFERROR to create complex formulas.
