{"id":3350,"date":"2014-03-03T21:39:35","date_gmt":"2014-03-03T16:09:35","guid":{"rendered":"http:\/\/trumpexcel.com\/?page_id=3350"},"modified":"2024-08-20T18:15:05","modified_gmt":"2024-08-20T12:45:05","slug":"excel-iferror-function","status":"publish","type":"post","link":"https:\/\/trumpexcel.com\/excel-iferror-function\/","title":{"rendered":"Excel IFERROR Function | Formula Examples + FREE Video"},"content":{"rendered":"<p>When you work with data and formulas in Excel, you&#8217;re bound to encounter errors.<\/p>\n<p>To handle errors, Excel has provided a useful function &#8211; the IFERROR function.<\/p>\n<p>Before we get into the mechanics of using the IFERROR function in Excel, let&#8217;s first go through the different kinds of errors you can encounter when working with formulas.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-light-blue ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">This Tutorial Covers:<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#Types-of-Errors-in-Excel\" >Types of Errors in Excel<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#NA-Error\" >#N\/A Error<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#DIV0-Error\" >#DIV\/0! Error<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#VALUE-Error\" >#VALUE! Error<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#REF-Error\" >#REF! Error<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#NAME-ERROR\" >#NAME ERROR<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#NUM-ERROR\" >#NUM ERROR<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#Excel-IFERROR-Function-%E2%80%93-An-Overview\" >Excel IFERROR Function &#8211; An Overview<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#IFERROR-Function-Syntax\" >IFERROR Function Syntax<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#Excel-IFERROR-Function-%E2%80%93-Examples\" >Excel IFERROR Function &#8211;\u00a0Examples<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#Example-1-%E2%80%93-Return-Blank-Cell-Instead-of-Error\" >Example 1 &#8211; Return Blank Cell Instead of Error<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#Example-2-%E2%80%93-Return-%E2%80%98Not-Found-when-VLOOKUP-Cant-Find-a-Value\" >Example 2\u00a0&#8211; Return &#8216;Not Found&#8217; when VLOOKUP Can&#8217;t Find a Value<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#Example-3-%E2%80%93-Return-0-in-case-of-an-Error\" >Example 3 &#8211; Return 0 in case of an Error<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#Example-4-%E2%80%93-Using-Nested-IFERROR-with-VLOOKUP\" >Example 4 &#8211; Using Nested IFERROR with VLOOKUP<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#Excel-IFERROR-Function-%E2%80%93-VIDEO\" >Excel IFERROR Function\u00a0&#8211; VIDEO<\/a><\/li><\/ul><\/nav><\/div>\n\n<h2><span class=\"ez-toc-section\" id=\"Types-of-Errors-in-Excel\"><\/span>Types of Errors in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Knowing the errors in Excel will better equip you to identify the possible reason and the best way to handle these.<\/p>\n<p>Below are the types of errors you might find in Excel.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"NA-Error\"><\/span>#N\/A Error<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>This is called the \u2018Value Not Available\u2019 error.<\/p>\n<p>You will see this when you use a lookup formula and it can\u2019t find the value (hence Not Available).<\/p>\n<p>Below is an example where I use the\u00a0<a href=\"https:\/\/trumpexcel.com\/excel-vlookup-function\/\" target=\"_blank\" rel=\"noopener noreferrer\">VLOOKUP formula<\/a>\u00a0to find the price of an item, but it returns an error when it can\u2019t find that item in the table array.<\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-23032 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Not-Available-Error.png\" alt=\"Excel IFERROR Function - Not Available Error\" \/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"DIV0-Error\"><\/span>#DIV\/0! Error<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>You\u2019re likely to see this error when a number is divided by 0.<\/p>\n<p>This is called the division error. In the below example, it gives a <a href=\"https:\/\/trumpexcel.com\/div-error-in-excel\/\" target=\"_blank\" rel=\"noopener\">#DIV\/0! error<\/a> as the quantity value (the divisor in the formula) is 0.<\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-23033 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Division-Error-in-Excel.png\" alt=\"Division Error in Excel\" \/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"VALUE-Error\"><\/span>#VALUE! Error<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>The <a href=\"https:\/\/trumpexcel.com\/fix-value-error-in-excel\/\" target=\"_blank\" rel=\"noopener\">value error<\/a> occurs when you use an incorrect data type in a formula.<\/p>\n<p>For example, in the below example, when I try to add cells that have numbers and character A, it gives the value error.<\/p>\n<p>This happens as you can only add numeric values, but instead, I tried adding a number with a text character.<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"size-full wp-image-23034 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Value-Error-in-Excel.png\" alt=\"Value Error in Excel\" width=\"344\" height=\"245\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Value-Error-in-Excel.png 344w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Value-Error-in-Excel-50x36.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Value-Error-in-Excel-250x178.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Value-Error-in-Excel-140x100.png 140w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Value-Error-in-Excel-281x200.png 281w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Value-Error-in-Excel-211x150.png 211w\" sizes=\"(max-width: 344px) 100vw, 344px\" \/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"REF-Error\"><\/span>#REF! Error<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>This is called the <a href=\"https:\/\/trumpexcel.com\/ref-error-in-excel\/\" target=\"_blank\" rel=\"noopener noreferrer\">reference error<\/a> and you will see this when the reference in the formula is no longer valid. This could be the case when the formula refers to a cell reference and that cell reference does not exist (happens when you delete a row\/column or worksheet that was referred to in the formula).<\/p>\n<p>In the below example, while the original formula was =A2\/B2, when I deleted Column B, all the references to it became #REF! and it also gave the #REF! error as the result of the formula.<\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-23035 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Reference-Error-in-Excel.png\" alt=\"Reference Error in Excel\" width=\"342\" height=\"314\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Reference-Error-in-Excel.png 342w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Reference-Error-in-Excel-327x300.png 327w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Reference-Error-in-Excel-50x46.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Reference-Error-in-Excel-250x230.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Reference-Error-in-Excel-109x100.png 109w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Reference-Error-in-Excel-218x200.png 218w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Reference-Error-in-Excel-163x150.png 163w\" sizes=\"(max-width: 342px) 100vw, 342px\" \/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"NAME-ERROR\"><\/span>#NAME ERROR<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>This error is likely to a result of a misspelled function.<\/p>\n<p>For example, if instead of VLOOKUP, you by mistake use VLOKUP, it will give a <a href=\"https:\/\/trumpexcel.com\/name-error-excel\/\" target=\"_blank\" rel=\"noopener\">name error<\/a>.<\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-23036 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/NAME-Error-in-Excel.png\" alt=\"NAME Error in Excel\" width=\"577\" height=\"253\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/NAME-Error-in-Excel.png 577w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/NAME-Error-in-Excel-50x22.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/NAME-Error-in-Excel-250x110.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/NAME-Error-in-Excel-228x100.png 228w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/NAME-Error-in-Excel-400x175.png 400w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/NAME-Error-in-Excel-342x150.png 342w\" sizes=\"(max-width: 577px) 100vw, 577px\" \/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"NUM-ERROR\"><\/span>#NUM ERROR<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Num error can occur if you try and calculate a very large value in Excel. For example,\u00a0<span class=\"default-formula-text-color\" dir=\"auto\">=<\/span><span class=\"number\" dir=\"auto\">187<\/span><span class=\"default-formula-text-color\" dir=\"auto\">^<\/span><span class=\"number\" dir=\"auto\">549 will return a number error.<\/span><\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-23037 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/NUM-Error-in-Excel.png\" alt=\"NUM Error in Excel\" \/><\/p>\n<p>Another situation where you can get the NUM error is when you give a non-valid number argument to a formula. For example, if you&#8217;re <a href=\"https:\/\/trumpexcel.com\/calculate-square-root-in-excel\/\" target=\"_blank\" rel=\"noopener noreferrer\">calculating the Square Root<\/a> if a number and you give a negative number as the argument, it will return a number error.<\/p>\n<p>For example, in the case of Square Root function, if you give a negative number as the argument, it will return a number error (as shown below).<\/p>\n<p>While I have shown only a couple of examples here, there can be many other reasons that can lead to errors in Excel. When you get errors in Excel, you can&#8217;t just leave it there. If the data is further used in calculations, you need to make sure the errors are handled the right way.<\/p>\n<p>Excel IFERROR function is a great way to handle all types of errors in Excel.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Excel-IFERROR-Function-%E2%80%93-An-Overview\"><\/span><span style=\"color: #800000;\">Excel IFERROR Function &#8211; An Overview<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Using the IFERROR function, you can specify what you want the formula to return instead of the\u00a0error. If the formula does not return an\u00a0error, then its own result is returned.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-3258 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/IFERROR-FORMULA-EXCEL.png\" alt=\"EXCEL IFERROR FUNCTION - What it does! \" width=\"450\" height=\"327\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/IFERROR-FORMULA-EXCEL.png 752w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/IFERROR-FORMULA-EXCEL-413x300.png 413w\" sizes=\"(max-width: 450px) 100vw, 450px\" \/><\/p>\n<h2><span class=\"ez-toc-section\" id=\"IFERROR-Function-Syntax\"><\/span><span style=\"color: #800000;\">IFERROR Function Syntax<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>=IFERROR(value, value_if_error)<\/p>\n<p><strong>Input Arguments<\/strong><\/p>\n<ul>\n<li><strong>value &#8211;\u00a0<\/strong>this is the argument that is checked for the error. In most cases, it is either a formula or a cell reference.<\/li>\n<li><b>value_if_error<\/b> &#8211;\u00a0this is the value that is returned if there is an error. The following error types evaluated: #N\/A, #REF!, #DIV\/0!, #VALUE!, #NUM!, #NAME?, and #NULL!.<\/li>\n<\/ul>\n<p><strong>Additional Notes:<\/strong><\/p>\n<ul>\n<li>If you use &#8220;&#8221; as the value_if_error argument, the cell displays nothing in case of an error.<\/li>\n<li>If the value or value_if_error argument refers to an empty cell, it is treated as an empty string value by the Excel IFERROR function.<\/li>\n<li>If the value argument is an array formula, IFERROR will return an array of results for each item in the range specified in value.<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Excel-IFERROR-Function-%E2%80%93-Examples\"><\/span><span style=\"color: #800000;\">Excel IFERROR Function &#8211;\u00a0Examples<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Here are three examples of using IFERROR function in Excel.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Example-1-%E2%80%93-Return-Blank-Cell-Instead-of-Error\"><\/span><span style=\"color: #800000;\"><span style=\"text-decoration: underline;\">Example 1<\/span> &#8211; Return Blank Cell Instead of Error<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>If you have functions that may return an error, you can wrap it within the IFERROR function and specify blank as the value to return in case of an error.<\/p>\n<p>In the example shown below, the result in D4 is the #DIV\/0! error as the divisor is 0.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-18081 size-full\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-divided-by-zero.png\" alt=\"Using Excel IFERROR function to remove error values\" width=\"317\" height=\"195\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-divided-by-zero.png 317w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-divided-by-zero-50x31.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-divided-by-zero-250x154.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-divided-by-zero-163x100.png 163w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-divided-by-zero-244x150.png 244w\" sizes=\"(max-width: 317px) 100vw, 317px\" \/><\/p>\n<p>In this case, you can use the following formula to return blank instead of the ugly DIV error.<\/p>\n<p>=IFERROR(A1\/A2,&#8221;&#8221;)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-18083 size-full\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-blank.png\" alt=\"Returning a blank using the IFERROR function in Excel\" width=\"390\" height=\"192\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-blank.png 390w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-blank-50x25.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-blank-250x123.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-blank-203x100.png 203w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-blank-305x150.png 305w\" sizes=\"(max-width: 390px) 100vw, 390px\" \/><\/p>\n<p>This IFERROR function would check whether the calculation leads to an error. If it does, it simply returns a blank as specified in the formula.<\/p>\n<p>Here, you can also specify any other string or formula to display instead of the blank.<\/p>\n<p>For example, the below formula would return the text &#8220;Error&#8221;, instead of the blank cell.<\/p>\n<p>=IFERROR(A1\/A2,&#8221;Error&#8221;)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-18082 size-full\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-returns-error.png\" alt=\"Using Excel Iferror function to return text in a cell\" width=\"421\" height=\"190\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-returns-error.png 421w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-returns-error-50x23.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-returns-error-250x113.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-returns-error-222x100.png 222w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-returns-error-400x181.png 400w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-returns-error-332x150.png 332w\" sizes=\"(max-width: 421px) 100vw, 421px\" \/><\/p>\n<p>Note: If you are using Excel 2003 or a\u00a0prior version, you will not find the IFERROR function in it. In such cases, you need to use the combination of <a href=\"https:\/\/trumpexcel.com\/excel-if-function\/\" target=\"_blank\" rel=\"noopener noreferrer\">IF function<\/a> and <a href=\"https:\/\/trumpexcel.com\/excel-is-function\/\">ISERROR<\/a> function.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Example-2-%E2%80%93-Return-%E2%80%98Not-Found-when-VLOOKUP-Cant-Find-a-Value\"><\/span><span style=\"color: #800000;\"><span style=\"text-decoration: underline;\">Example 2<\/span>\u00a0&#8211; Return &#8216;Not Found&#8217; when VLOOKUP Can&#8217;t Find a Value<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>When you use the Excel <a href=\"https:\/\/trumpexcel.com\/excel-vlookup-function\/\" target=\"_blank\" rel=\"noopener noreferrer\">VLOOKUP Function<\/a>, and it can&#8217;t find the lookup value in the specified range, it would return the #N\/A error.<\/p>\n<p>For example, below is a data set of student names and their marks. I have used the VLOOKUP function\u00a0to fetch the marks of three students (in D2, D3, and D4).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-18084 size-full\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup.png\" alt=\"Using iferror functiont to handle NA error in Excel\" width=\"512\" height=\"394\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup.png 512w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup-390x300.png 390w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup-50x38.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup-250x192.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup-130x100.png 130w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup-260x200.png 260w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup-195x150.png 195w\" sizes=\"(max-width: 512px) 100vw, 512px\" \/><\/p>\n<p>While the VLOOKUP formula in the above example finds the names of first two students, it can&#8217;t find Josh&#8217;s name on the list and hence it returns the #N\/A error.<\/p>\n<p>Here, we can use the IFERROR function to return a blank or some meaningful text instead of the error.<\/p>\n<p>Below is the formula that will return &#8216;Not Found&#8217; instead of the error.<\/p>\n<p>=IFERROR(VLOOKUP(D2,$A$2:$B$12,2,0),&#8221;Not Found&#8221;)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-18085 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup-Not-Found.png\" alt=\"excel-iferror-function-vlookup-not-found\" width=\"607\" height=\"388\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup-Not-Found.png 607w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup-Not-Found-469x300.png 469w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup-Not-Found-50x32.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup-Not-Found-250x160.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup-Not-Found-156x100.png 156w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup-Not-Found-313x200.png 313w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-Vlookup-Not-Found-235x150.png 235w\" sizes=\"(max-width: 607px) 100vw, 607px\" \/><\/p>\n<p>Note that you can also use IFNA instead of IFERROR with VLOOKUP. While IFERROR would treat all kinds of error values, IFNA would only work on the #N\/A errors and wouldn&#8217;t work with other errors.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Example-3-%E2%80%93-Return-0-in-case-of-an-Error\"><\/span><span style=\"color: #800000;\"><span style=\"text-decoration: underline;\">Example<\/span><span style=\"text-decoration: underline;\"> 3<\/span> &#8211; Return 0 in case of an Error<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>If you don&#8217;t specify the value to return by IFERROR in the\u00a0case of an error, it would automatically return 0.<\/p>\n<p>For example,\u00a0if I divide 100 with 0 as shown below, it would return an error.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-18087 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-error-when-div-by-0.png\" alt=\"excel-iferror-function-error-when-div-by-0\" width=\"319\" height=\"189\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-error-when-div-by-0.png 319w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-error-when-div-by-0-50x30.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-error-when-div-by-0-250x148.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-error-when-div-by-0-169x100.png 169w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-error-when-div-by-0-253x150.png 253w\" sizes=\"(max-width: 319px) 100vw, 319px\" \/><\/p>\n<p>However, if I use the below IFERROR function, it would return a 0 instead. Note that you still need to use a comma after the first argument.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-18086 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-comma.png\" alt=\"excel-iferror-function-comma\" width=\"379\" height=\"191\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-comma.png 379w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-comma-50x25.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-comma-250x126.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-comma-198x100.png 198w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2014\/03\/Excel-IFERROR-Function-comma-298x150.png 298w\" sizes=\"(max-width: 379px) 100vw, 379px\" \/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Example-4-%E2%80%93-Using-Nested-IFERROR-with-VLOOKUP\"><\/span><span style=\"color: #800000;\"><span style=\"text-decoration: underline;\">Example 4<\/span> &#8211; Using Nested IFERROR with VLOOKUP<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Sometimes when using VLOOKUP, you may have to look through the fragmented table of arrays. For example, suppose you have the sales transaction records in 2 separate worksheets and you want to look-up an item number and see it&#8217;s value.<\/p>\n<p>Doing this requires using nested IFERROR with VLOOKUP.<\/p>\n<p>Suppose you have a dataset as shown below:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-22084 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Nested-IFERROR-with-VLOOKUP-dataset.png\" alt=\"Nested IFERROR with VLOOKUP dataset\" width=\"635\" height=\"179\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Nested-IFERROR-with-VLOOKUP-dataset.png 635w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Nested-IFERROR-with-VLOOKUP-dataset-600x169.png 600w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Nested-IFERROR-with-VLOOKUP-dataset-50x14.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Nested-IFERROR-with-VLOOKUP-dataset-250x70.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Nested-IFERROR-with-VLOOKUP-dataset-400x113.png 400w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Nested-IFERROR-with-VLOOKUP-dataset-350x99.png 350w\" sizes=\"(max-width: 635px) 100vw, 635px\" \/><\/p>\n<p>In this case, to find the score for Grace, you need to use the below nested IFERROR formula:<\/p>\n<pre><strong>=IFERROR(VLOOKUP(G3,$A$2:$B$5,2,0),IFERROR(VLOOKUP(G3,$D$2:$E$5,2,0),\"Not Found\"))<\/strong><\/pre>\n<p>This kind of formula nesting ensure that you get the value from either of the table and any error returned is handled.<\/p>\n<p>Note that in case the tables\u00a0are on the same worksheet, however, in a real-life example, it likely to be on different worksheets.<\/p>\n<h2 style=\"text-align: center;\"><span class=\"ez-toc-section\" id=\"Excel-IFERROR-Function-%E2%80%93-VIDEO\"><\/span><span style=\"color: #800000;\">Excel IFERROR Function\u00a0&#8211; VIDEO<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><iframe title=\"IFERROR Formula in Excel\" width=\"1200\" height=\"675\" src=\"https:\/\/www.youtube.com\/embed\/K96rNR-F_60?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen><\/iframe><\/p>\n<p><strong>Related Excel Functions:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/trumpexcel.com\/excel-and-function\/\" target=\"_blank\" rel=\"noopener noreferrer\">Excel AND Function<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/excel-or-function\/\" target=\"_blank\" rel=\"noopener noreferrer\">Excel OR Function<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/excel-not-function\/\" target=\"_blank\" rel=\"noopener noreferrer\">Excel NOT Function<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/excel-if-function\/\" target=\"_blank\" rel=\"noopener noreferrer\">Excel IF Function<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/excel-ifs-function\/\" target=\"_blank\" rel=\"noopener noreferrer\">Excel IFS Function<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/excel-false-function\/\" target=\"_blank\" rel=\"noopener noreferrer\">Excel FALSE Function<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/excel-true-function\/\" target=\"_blank\" rel=\"noopener noreferrer\">Excel TRUE Function<\/a>.<\/li>\n<\/ul>\n<p><strong>You May Also Like the Following Excel Tutorials:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/trumpexcel.com\/iferror-vlookup\/\" target=\"_blank\" rel=\"noopener noreferrer\">Use IFERROR with VLOOKUP to Get Rid of #N\/A Errors<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/excel-formula-debugging\/\" target=\"_blank\" rel=\"noopener noreferrer\">Identify Errors Using Excel Formula Debugging.<\/a><\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/median-if-excel\/\">Calculate MEDIAN IF in Excel<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>When you work with data and formulas in Excel, you&#8217;re bound to encounter errors. To handle errors, Excel has provided a useful function &#8211; the IFERROR function. Before we get into the mechanics of using the IFERROR function in Excel, let&#8217;s first go through the different kinds of errors you can encounter when working with &#8230; <\/p>\n<p class=\"read-more-container\"><a title=\"Excel IFERROR Function | Formula Examples + FREE Video\" class=\"read-more button\" href=\"https:\/\/trumpexcel.com\/excel-iferror-function\/#more-3350\" aria-label=\"Read more about Excel IFERROR Function | Formula Examples + FREE Video\">Read more<\/a><\/p>\n","protected":false},"author":80,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[153],"tags":[19],"class_list":["post-3350","post","type-post","status-publish","format-standard","hentry","category-excel-functions","tag-excel-how-tos"],"_links":{"self":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/posts\/3350","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/users\/80"}],"replies":[{"embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/comments?post=3350"}],"version-history":[{"count":0,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/posts\/3350\/revisions"}],"wp:attachment":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/media?parent=3350"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/categories?post=3350"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/tags?post=3350"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}