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.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-1.png” alt=”Google Sheets #VALUE! error \u2014 =A2*B2 and an IF formula return #VALUE! because Operand B holds text” title_text=”=A2*B2 and an IF formula return #VALUE! because Operand B holds text” align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
#REF! error<\/h3>\n
Usually, this shows up when you intentionally or accidentally delete rows, columns, or sheets that are referenced in other cells.<\/p>\n
If I remove the Operand A<\/b> column from the previous example, a #REF! error pops up as shown below.<\/p>\n
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<\/a>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-2.png” alt=”Google Sheets #REF! error \u2014 the formulas show #REF! after the Operand A column was deleted” title_text=”The formulas show #REF! after the Operand A column was deleted” align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Google Sheets generates this error when it comes across a function that it doesn\u2019t recognize.<\/p>\n To demonstrate that further, I\u2019ll try using WHATIF (which is not an actual function) instead of IF.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-3.png” alt=”Google Sheets #NAME? error \u2014 a formula that calls WHATIF, which is not a function, returns #NAME?” title_text=”A formula that calls WHATIF, which is not a function, returns #NAME?” align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 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.<\/p>\n A negative number is not expected in the SQRT function, hence the error.<\/p>\n 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.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-4.png” alt=”Google Sheets #NUM! error \u2014 =sqrt(A2) of -25 and =POWER(A3,5555) both return #NUM!” title_text=”=sqrt(A2) of -25 and =POWER(A3,5555) both return #NUM!” align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This is another common error usually associated with lookup functions when they cannot find a match for the value they are asked to find.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-5.png” alt=”Google Sheets #N\/A error \u2014 VLOOKUP did not find the value Stephen in the Name column” title_text=”VLOOKUP did not find the value Stephen in the Name column” align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You get this error when a formula divides by zero or by an empty cell. It often shows up when you copy a division formula down a column and some rows have no data yet.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Google Sheets shows #ERROR! when it can’t parse a formula, usually because of a typo such as a missing comma, an extra parenthesis, or an unclosed quote. IFERROR can’t hide it, because the whole formula fails to parse, IFERROR included. You have to fix the formula itself. Learn how in How to solve formula parse errors in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_text module_id=”how” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n If you are using a function and it keeps returning errors, you can \u201cwrap\u201d the formula with IFERROR. This will give you a cleaner spreadsheet with data that\u2019s easier to read.<\/p>\n It can be used with many common functions including Query<\/a> and Vlookup<\/a>.<\/p>\n IFERROR works by scanning a formula\u2019s 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 \u201cif error return blank\u201d. If the formula has no error, it simply returns the intended output.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IFERROR(value, [value_if_error])<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_id=”how_to” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the first example below, you can see the effect of adding IFERROR to the beginning of the function.<\/p>\n I\u2019m working with the #N\/A error that I showed previously. As you\u2019ll recall, this appears when a lookup function can\u2019t find the input it\u2019s searching for.<\/p>\n Here, I\u2019ve added the IFERROR function before the VLOOKUP.<\/p>\n If there is no error, the IFERROR function simply outputs the VLOOKUP output that it would normally generate.<\/p>\n If there is an error, however, instead of simply displaying an error message, I ask it to display the text \u201cinvalid input\u201d. This enables you to avoid ugly and unexplained error messages in your spreadsheet.<\/p>\n In the example below, I\u2019ve done this with the following formula:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =Iferror(Vlookup(“Stephen”,A2:B8,2,False),”Invalid input”)<\/strong>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-6.png” alt=”IFERROR Google Sheets \u2014 IFERROR around VLOOKUP returns Invalid input instead of #N\/A” title_text=”IFERROR around VLOOKUP returns Invalid input instead of #N\/A” align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the following example, we\u2019ll see how to make #div\/0 show as blank.<\/p>\n As you might have guessed, this occurs when you divide a number by 0.<\/p>\n 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.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-7.png” alt=”Google Sheets #DIV\/0! error \u2014 =C6\/B6 returns #DIV\/0! for a sales quantity of 0″ title_text=”=C6\/B6 returns #DIV\/0! for a sales quantity of 0″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now, I\u2019ll modify the formula to include the IFERROR function.<\/p>\n As you can see below, this gets rid of the error message.<\/p>\n By adding IFERROR to the front of the formula, it simply returns a blank cell.<\/p>\n To show 0 instead of a blank, add 0 as the second argument, for example =IFERROR(A2\/B2, 0).<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-8.png” alt=”IFERROR Google Sheets \u2014 =IFERROR(C6\/B6) leaves the revenue per sale cell blank” title_text=”=IFERROR(C6\/B6) leaves the revenue per sale cell blank” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Yes, you can use IFERROR with arrays <\/b>by using the array formula function<\/a>. Wrap IFERROR inside ARRAYFORMULA, as in the formula below. It divides each value in column A by the value in the same row of column B and leaves a blank wherever the division fails.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =ARRAYFORMULA(IFERROR(A2:A\/B2:B, “”))<\/p>\n [\/et_pb_text][et_pb_text module_id=”nest-iferror-to-try-a-second-lookup” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Follow the steps below to nest multiple IFERROR functions to try different options if the value you want isn\u2019t found. For example, if you\u2019re unsure whether you have a first or last name, you can check both columns in turn.<\/p>\n 1.<\/strong> Type the IFERROR function and add your lookup function to find the search key in the \u2018last_name\u2019 column. Close the parenthesis and add a comma.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/iferror-google-sheets-first-iferror-lookup.webp” alt=”IFERROR Google Sheets \u2014 first IFERROR wrapping a VLOOKUP on the last_name column” title_text=”First IFERROR wrapping a VLOOKUP on the last_name column” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> For the second parameter, add another IFERROR function followed by the lookup function. In this case, you want to look for the search key in the \u2018first_name\u2019 column. Close the parenthesis and add another comma.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/iferror-google-sheets-second-iferror-lookup.webp” alt=”IFERROR Google Sheets \u2014 second IFERROR with a VLOOKUP on the first_name column” title_text=”Second IFERROR with a VLOOKUP on the first_name column” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3.<\/strong> Finally, add the message you want to return if neither lookup is successful.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/iferror-google-sheets-add-value-if-error-3.webp” alt=”IFERROR Google Sheets \u2014 nested formula ending with the No data found message” title_text=”Nested formula ending with the No data found message” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 4.<\/strong> Close both parentheses and hit \u2018Enter\u2019 to see the result. Accept the Autofill suggestion or drag down the fill handle to copy the formula.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/iferror-google-sheets-nested-iferror-lookups-result.webp” alt=”Nested IFERROR in Google Sheets \u2014 amounts found by last or first name, No data found for Johnson” title_text=”Amounts found by last or first name, No data found for Johnson” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Each IFERROR in the nested formula catches every error type. If #N\/A is the only error you expect, use IFNA instead: it replaces #N\/A and still shows other errors, such as a broken reference. This guide to IFNA in Google Sheets<\/a> compares the two functions.<\/p>\n [\/et_pb_text][et_pb_text module_id=”a” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you\u2019ve seen, the IFERROR function is great for masking error warnings and replacing them with clean, blank cells or a meaningful message or value.<\/p>\n Be careful, however, with when and where you apply this function. Hiding all errors can make it difficult to troubleshoot<\/strong> a spreadsheet when the error is affecting functionality. For instance, it doesn\u2019t 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!<\/p>\n 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.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":39,"featured_media":27679,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"Google Sheets is built in such a way that the expressions return appropriate values in accordance with the given inputs. However, more often than not, we may encounter certain unpleasant errors. This happens due to one or more reasons as explained in this article. To handle such errors, we have the IFERROR<\/a> formula.\n#NAME? error<\/h3>\n
#NUM! error<\/h3>\n
#N\/A error<\/h3>\n
#DIV\/0! error<\/h3>\n
#ERROR! error<\/h3>\n
How IFERROR works<\/h2>\n
Handle Google Sheets errors with the IFERROR function<\/h3>\n
IFERROR syntax<\/h3>\n
\n
How to use IFERROR<\/strong><\/h2>\n
How to get rid of n\/a<\/h3>\n
How to get rid of div\/0<\/span><\/h3>\n
Can you use IFERROR with arrays?<\/h3>\n
Nest IFERROR to try a second lookup<\/h2>\n
A note about hiding errors<\/h2>\n
Did you like this post?<\/h2>\n
Errors? How do they look like?<\/h3>\n
The #VALUE! error<\/h5>\nPerhaps, this is the most frequent error type. It occurs when we use an incorrect data type for the expected input arguments as illustrated below.\n\n
![\"IFERROR](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/iferror1.png\")
\nThe #REF! error<\/h5>\nUsually, this springs up when we intentionally or accidentally delete rows, columns or worksheets, that are referenced in other cells. From the above examples, let's try removing the \"Operand A\" column, and this is what happens. Notice that even within the formula, the #REF! shows, indicating it lost a reference in that placeholder.\n\n
![\"IFERROR](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/iferror2.png\")
\nThe #NAME! error<\/h5>\nGoogle Sheets throws this up when it sees any function that it doesn't recognize. To understand that further, let's try by using WHATIF, instead of IF in the third row.\n\n
![\"IFERROR](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/iferror3.png\")
\nThe #NUM! error<\/h5>\nWhen the expected input arguments do not match with what we provide, this might show up. For instance, consider the first example in the snapshot below. A negative number is not expected in the SQRT function, hence the error. This can also surface when the number to display is out of range, as shown in the snapshot below.\n\n
![\"IFERROR](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/iferror4.png\")
\nThe #N\/A! error<\/h5>\nThis is another common error usually associated with the lookup functions when they cannot find a match for the value they are asked to find.\n\n
![\"IFERROR](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/iferror5.png\")
\n\nWe can give these errors a meaningful facet, using the handy IFERROR formula.\nSyntax<\/h3>\nIFERROR(value, [value_if_error])<\/strong><\/span>\n
\n \t
Examples: IFERROR formula<\/h3>\nGiven below are a few use cases where we put this formula to use.\n\n
![\"IFERROR](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/iferror6.png\")
\n\nTherefore, as we can see, the IFERROR formula is used to mask errors with rather meaningful values. But it isn't ideal to mask every error - doing that will make it difficult to troubleshoot. For instance, it doesn't really make sense to mask #REF! error, because we need to be aware which cell reference is lost.\n\nRead the blog post below to learn more about a similar formula \"How to solve the #REF! Error on spreadsheets<\/a><\/strong>\"","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-3066","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/3066","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/39"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=3066"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/3066\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/27679"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=3066"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=3066"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=3066"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}