Cómo utilizar la función IFERROR en Google Sheets

Instead of displaying ugly error messages, IFERROR enables you to clean up your spreadsheet and replace errors with other values. This could be a blank cell, a number, or a relevant message that you and your team can understand.

Google Sheets is extremely user-friendly and reliable but if you use it regularly, you will eventually run into an error. This can happen for a variety of reasons, which we’ll explore in this article.

Google Sheets error handling is a fundamental concept when creating spreadsheets. And the IFERROR formula is the best tool for the job.

Common errors in Google Sheets

These errors show up whenever a formula breaks. Understanding them is the first step in choosing whether to fix them or to hide them with IFERROR.

The #VALUE! error

This is probably the most frequent type of error in Google Sheets. It occurs when you use an incorrect data type for the expected input arguments, as illustrated below.

#REF! error

Usually, this shows up when you intentionally or accidentally delete rows, columns, or sheets that are referenced in other cells.

If I remove the Operand A column from the previous example, a #REF error pops up as shown below.

Notice that the #REF! error even appears inside the formula, indicating that it lost a reference in that placeholder. Learn more about this particular issue in How to solve the #REF! Error in spreadsheets.

#NAME! error

Google Sheets generates this error when it comes across a function that it doesn’t recognize.

To demonstrate that further, I’ll try using WHATIF (which is not an actual function) instead of IF.

#NUM! error

When the expected input arguments do not match with what you provide, the num error might show up. Take a look at the first example in the snapshot below.

A negative number is not expected in the SQRT function, hence the error.

This error can also appear when the number to display is out of range, as seen in the second example where the result is too large for the spreadsheet to compute.

#N/A! error

This is another common error usually associated with lookup functions when they cannot find a match for the value they are asked to find.

How IFERROR works

Handle Google Sheets errors with the IFERROR function

If you are using a function and it keeps returning errors, you can “wrap” the formula with IFERROR. This will give you a cleaner spreadsheet with data that’s easier to read.

It can be used with many common functions including Consulta y Vlookup.

IFERROR works by scanning a formula’s output for errors. If it finds one, it replaces it with any value you specify. This function is usually used to hide errors following the logic “if error return blank”. If the formula has no error, it simply returns the intended output.

IFERROR Syntax

• valor – this is the argument that the function checks for errors. This can be any cell reference or a formula.
• value_if_error – this is the value the function returns if there is an error. It can be a number, word, or a text string such as “Item sold out”. This is an optional parameter so if you don’t specify anything, the IFERROR function will return a blank cell when it encounters an error.

How to use IFERROR

How to get rid of n/a

In the first example below, you can see the effect of adding IFERROR to the beginning of the function.

I’m working with the #N/A! error that I showed previously. As you’ll recall, this appears when a lookup function can’t find the input it’s searching for.

Here, I’ve added the IFERROR function before the VLOOKUP.

If there is no error, the IFERROR function simply outputs the VLOOKUP output that it would normally generate.

If there is an error, however, instead of simply displaying an error message, I ask it to display the text “invalid input”. This enables you to avoid ugly and unexplained error messages in your spreadsheet.

In the example below, I’ve done this with the following formula:

How to get rid of div/0

In the following example, we’ll see how to make #div/0 show as blank.

As you might have guessed, this occurs when you divide a number by 0.

This is quite common when you have a function that you drag down to apply to a group of cells, and some of the cells are missing data or contain zeros.

Now, I’ll modify the formula to include the IFERROR function.

As you can see below, this gets rid of the error message.

By adding IFERROR to the front of the formula, it simply returns a blank cell.

Can you use IFERROR with arrays?

Yes, you can use IFERROR with arrays by using the array formula function. To do it but you must add it within the formula as shown below.

A note about hiding errors

As you’ve seen, the IFERROR function is great for masking error warnings and replacing them with clean, blank cells or a meaningful message or value.

Be careful, however, with when and where you apply this function. Hiding all errors can make it difficult to troubleshoot a spreadsheet when the error is affecting functionality. For instance, it doesn’t make sense to mask a #REF! error, because you need to know which cell reference is lost in order to fix the error and keep your spreadsheet working properly!

¿Te ha gustado este post?

If you found this article helpful, please share it with your colleagues and fellow spreadsheet-users via the social media buttons on the left! For more Google Sheets tips, check out our other articles below or our 5 best tips for structuring your spreadsheet data.