Replace Excel Errors with an alternate result
john lilleystone • April 11, 2020
Use Excel's IFERROR function to replace error's with an alternate result
Get rid of nasty divide errors and tidy up your reports.
If you have an Excel formula that returns an error (i.e. 1 / 0 = #DIV!0 or a VLOOKUP can’t find a match and returns #N/A) it can make your report look ugly. Tidy these errors up by replacing them with something more aesthetically appealing. Here’s an example;
I have a report which is using a VLOOKUP to another sheet to pull in some numbers;
If you have an Excel formula that returns an error (i.e. 1 / 0 = #DIV!0 or a VLOOKUP can’t find a match and returns #N/A) it can make your report look ugly. Tidy these errors up by replacing them with something more aesthetically appealing. Here’s an example;
I have a report which is using a VLOOKUP to another sheet to pull in some numbers;
It shows #N/A for cells where it cannot find an exact match. Looks nasty!
We can tidy it up by wrapping the VLOOKUP formula in a condition called IFERROR. The original formula was this =(VLOOKUP(A278,Sheet2!A:G,7,FALSE)). But if I use =IFERROR(VLOOKUP(A278,Sheet2!A:G,7,FALSE),0) instead I get this;
We can tidy it up by wrapping the VLOOKUP formula in a condition called IFERROR. The original formula was this =(VLOOKUP(A278,Sheet2!A:G,7,FALSE)). But if I use =IFERROR(VLOOKUP(A278,Sheet2!A:G,7,FALSE),0) instead I get this;
Much better!
All this formula is saying is if Excel returns an error display 0 instead, if not then show the result. The alternate result can be text too. In this example I’ve returned zero but it could be anything – 5, 10, BANANAS. Let’s return BANANAS instead
=IFERROR(VLOOKUP(A278,Sheet2!A:G,7,FALSE),”BANANAS”)
All this formula is saying is if Excel returns an error display 0 instead, if not then show the result. The alternate result can be text too. In this example I’ve returned zero but it could be anything – 5, 10, BANANAS. Let’s return BANANAS instead
=IFERROR(VLOOKUP(A278,Sheet2!A:G,7,FALSE),”BANANAS”)
BANANAS – yum!
Use the RANDBETWEEN function in Excel to randomly generate numbers between a specified upper and lower threshold.
Don't waste time manually editing queries to move the date period on one month. This blog explains how to use DATEADD to dynamically shift the dates on one month on each refresh
Import text files into Excel and use the Text to Columns wizard to separate the data into columns
Extract values or strings from a cell from either the left, middle or right part of the cell
