{"id":31831,"date":"2021-02-25T11:21:42","date_gmt":"2021-02-25T10:21:42","guid":{"rendered":"https:\/\/blog.sheetgo.com\/?p=31831"},"modified":"2021-02-25T11:21:42","modified_gmt":"2021-02-25T10:21:42","slug":"iferror-excel","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/es\/blog\/excel-functions\/iferror-excel\/","title":{"rendered":"C\u00f3mo utilizar la funci\u00f3n IFERROR en Excel"},"content":{"rendered":"<p>[et_pb_section fb_built=&#8221;1&#8243; module_class=&#8221;sheetgo-post&#8221; _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][et_pb_row _builder_version=&#8221;4.21.2&#8243; _module_preset=&#8221;default&#8221; width_tablet=&#8221;&#8221; width_phone=&#8221;90%&#8221; width_last_edited=&#8221;on|phone&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][et_pb_column type=&#8221;4_4&#8243; _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<p>No matter how great you are with spreadsheets, sooner or later (most likely sooner) you\u2019re going to run into an error. This happens all the time, so it\u2019s no surprise that Excel has a function specifically to handle errors. <a href=\"https:\/\/support.microsoft.com\/en-us\/office\/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611\" target=\"_blank\" rel=\"noopener noreferrer\">The IFERROR function<\/a> prevents error messages from cluttering your spreadsheet, and even lets you customize what you want to happen when an error occurs. And of course, when there\u2019s no error, the IFERROR function lets your spreadsheet work like normal \u2013 it just steps in to handle any errors that pop up!<\/p>\n<p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<h3>Syntax<\/h3>\n<p>[\/et_pb_text][et_pb_text module_class=&#8221;spreadsheet-function&#8221; _builder_version=&#8221;4.16&#8243; border_width_left=&#8221;4px&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]=IFERROR(value, value_if_error)[\/et_pb_text][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<ul>\n<li><b>value<\/b> is the item that\u2019s being checked for an error<\/li>\n<li><b>value_if_error<\/b> is what the function returns when there is an error. This can be a text string, number, blank cell, or even another formula<\/li>\n<\/ul>\n<p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<h3>How to use the IFERROR Excel function<\/h3>\n<h4>Custom error message<\/h4>\n<p>To see the IFERROR Excel function in action, take a look at the example below. I have some running times and distances for a group of athletes, and I\u2019ve written a formula to show their average speed.<\/p>\n<p>Unfortunately, the data has some errors that translate into error messages in column D.<\/p>\n<p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/1.-IFERROR-data-w-errors.png&#8221; alt=&#8221;iferror excel 1&#8243; title_text=&#8221;1. IFERROR data w errors&#8221; align=&#8221;center&#8221; _builder_version=&#8221;4.21.2&#8243; _module_preset=&#8221;default&#8221; width=&#8221;100%&#8221; custom_margin=&#8221;0px|0px|0px|0px|true|true&#8221; custom_padding=&#8221;0px|0px|0px|0px|true|true&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<p>There\u2019s no question that this looks messy. Obviously the best thing to do is to fix the errors in the data, but sometimes that\u2019s not so easy, especially with large and complex datasets.<\/p>\n<p>So instead, I\u2019ll use the IFERROR function.<\/p>\n<p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/2.-IFERROR-custom-error-message.png&#8221; alt=&#8221;iferror excel 2&#8243; title_text=&#8221;2. IFERROR custom error message&#8221; align=&#8221;center&#8221; _builder_version=&#8221;4.21.2&#8243; _module_preset=&#8221;default&#8221; width=&#8221;100%&#8221; width_tablet=&#8221;100%&#8221; width_phone=&#8221;100%&#8221; width_last_edited=&#8221;on|phone&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<p>Now instead of an Excel-generated error message, I get a text string that tells me there\u2019s an error. This is achieved with the formula <b>=IFERROR(B2\/(C2\/60),&#8221;Error&#8221;)<\/b>.<\/p>\n<p>All I\u2019ve done is added the IFERROR function with the formula I was already using as the <b>value<\/b> parameter, and the text string \u201cError\u201d as the <b>value_if_error<\/b>.<\/p>\n<p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<h4>Turn errors into blanks<\/h4>\n<p>What if you don\u2019t want any error message at all? You can make the <b>value_if_error<\/b> a blank cell by typing \u201c\u201d to create an empty string.<\/p>\n<p>Now instead of error messages, there\u2019s nothing at all in those cells. Much cleaner!<\/p>\n<p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/3.-IFERROR-blank-errors.png&#8221; alt=&#8221;iferror excel 3&#8243; title_text=&#8221;3. IFERROR blank errors&#8221; align=&#8221;center&#8221; _builder_version=&#8221;4.21.2&#8243; _module_preset=&#8221;default&#8221; width=&#8221;100%&#8221; width_tablet=&#8221;100%&#8221; width_phone=&#8221;100%&#8221; width_last_edited=&#8221;on|tablet&#8221; min_height_tablet=&#8221;&#8221; min_height_phone=&#8221;&#8221; min_height_last_edited=&#8221;on|phone&#8221; height_tablet=&#8221;&#8221; height_phone=&#8221;&#8221; height_last_edited=&#8221;on|phone&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<h4>IFERROR with VLOOKUP<\/h4>\n<p>One of the most common uses of IFERROR is in combination with the <a href=\"https:\/\/www.sheetgo.com\/blog\/excel-functions\/vlookup-excel\/\">VLOOKUP function<\/a>. When you use VLOOKUP to search for an exact value, you\u2019ll get a #N\/A error if the function doesn\u2019t find a match. You can replace the error with a customized message to make your spreadsheet more comprehensive and professional!<\/p>\n<p>In this example, I\u2019m using VLOOKUP to find the sales of an employee. I search the spreadsheet for Gary, but that name isn\u2019t in the data, so I get an error.<\/p>\n<p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/4.-IFERROR-Vlookup-error.png&#8221; alt=&#8221;iferror excel vlookup error 4&#8243; title_text=&#8221;4. IFERROR Vlookup error&#8221; align=&#8221;center&#8221; _builder_version=&#8221;4.21.2&#8243; _module_preset=&#8221;default&#8221; width=&#8221;100%&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<p>Now I\u2019ll add the IFERROR function to the VLOOKUP function with the formula <b>=IFERROR(VLOOKUP(E2,A2:C11,3,FALSE),&#8221;Employee not found&#8221;)<\/b>.<\/p>\n<p>Now instead of a #N\/A error, I get an error message telling me that the employee name wasn\u2019t found.<\/p>\n<p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/5.-IFERROR-Vlookup-custom-error-message.png&#8221; alt=&#8221;iferror excel vlookup custom error message 5&#8243; title_text=&#8221;5. IFERROR Vlookup custom error message&#8221; align=&#8221;center&#8221; _builder_version=&#8221;4.21.2&#8243; _module_preset=&#8221;default&#8221; width=&#8221;100%&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<p>And in case you\u2019re wondering, if the employee name is there (so there\u2019s no error in the VLOOKUP) the function works normally!<\/p>\n<p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/6.-IFERROR-Vlookup-working-normally.png&#8221; alt=&#8221;iferror excel 6&#8243; title_text=&#8221;6. IFERROR Vlookup working normally&#8221; align=&#8221;center&#8221; _builder_version=&#8221;4.21.2&#8243; _module_preset=&#8221;default&#8221; width=&#8221;100%&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<p>The IFERROR Excel function is very simple, but it works wonders by making your spreadsheet free of unwelcome error messages!<\/p>\n<p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.16&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<\/p>\n<h3>Tips when using IFERROR in Excel<\/h3>\n<ul>\n<li>If you\u2019re adding it to an existing function, just put IFERROR( before the existing function, and then ,value_if_error) after it<\/li>\n<li>The IFERROR function handles all types of errors in Excel<\/li>\n<li>If the cell that you\u2019re evaluating is blank, it does not count as an error<\/li>\n<li>IFERROR also works for array functions<\/li>\n<\/ul>\n<p>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>No matter how great you are with spreadsheets, sooner or later (most likely sooner) you\u2019re going to run into an error. This happens all the time, so it\u2019s no surprise that Excel has a function specifically to handle errors. The IFERROR function prevents error messages from cluttering your spreadsheet, and even lets you customize what [&hellip;]<\/p>\n","protected":false},"author":40,"featured_media":31833,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[65],"tags":[39,28],"class_list":["post-31831","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-functions","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/31831","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/comments?post=31831"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/31831\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/31833"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=31831"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=31831"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=31831"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}