Cleanest Way to Handle Error Values

[mark5767]

Probably this has been discussed elsewhere, but I thought I would ask anyway.

Using index/match, what is the cleanest way to handle error values? People don't like to look at #N/A's all over the place so I am considering my options to replace them with blanks " " or 0's.

In the past with Excel 2003 I have used ISERROR the replace #N/A with " " for example:

=IF(ISERROR(INDEX(B$3:B$7, MATCH($A$22,$A$3:$A$7,0)))," ",INDEX(B$3:B$7, MATCH($A$22,$A$3:$A$7,0)))

With Excel 2007 there is the IFERROR function, but I'm not sure if it offers much improvement complexity wise over the ISERROR function. Does anyone have suggestions on simpler ways to handle error values returning from lookup/reference functions? Thank you!

 

[VoG]

Using IFERROR avoids having to compute the formula twice if there isn't an error

=IFERROR(INDEX(B$3:B$7, MATCH($A$22,$A$3:$A$7,0)),"")

 

[mark5767]

Wow VoG, that is cleaner, thanks!

I'm pretty new to Excel 2007 so still learning it's advantages over 2003. That looks to be a clear advantage with IFERROR versus ISERROR. When I'm back in the office next week (I only have Excel 03 at home here) I will try out the IFERROR function.

The spreadsheets I'm working with have literally thousands of index/match formulas set up computing twice to avoid returning error values. Replacing these with IFERROR to only compute one time could potentially streamline those files significantly... I hope! Thanks again!

 

[VoG]

You are welcome. I would imagine that your present set-up takes some time to recalculate. Using IFERROR should reduce that.

 

[T. Valko]

Probably this has been discussed elsewhere, but I thought I would ask anyway.

Using index/match, what is the cleanest way to handle error values? People don't like to look at #N/A's all over the place so I am considering my options to replace them with blanks " " or 0's.

In the past with Excel 2003 I have used ISERROR the replace #N/A with " " for example:


=IF(ISERROR(INDEX(B$3:B$7, MATCH($A$22,$A$3:$A$7,0)))," ",INDEX(B$3:B$7, MATCH($A$22,$A$3:$A$7,0)))


With Excel 2007 there is the IFERROR function, but I'm not sure if it offers much improvement complexity wise over the ISERROR function. Does anyone have suggestions on simpler ways to handle error values returning from lookup/reference functions? Thank you!

It all depends on the formula that's returning the error.

In Excel 2007 and later the new IFERROR function is nice in some applications but it is not the most efficient method to be used for ALL applications. It does help reduce the length of formulas, though.

In your example the error would be generated by the MATCH function (provided there are not already errors in any of the ranges).

So, we can just error trap against the MATCH function by first testing to see if the lookup value actually exists. There are a couple of ways to do that.

=IF(ISNA(MATCH($A$22,$A$3:$A$7,0)),"",INDEX(B$3:B$7,MATCH($A$22,$A$3:$A$7,0)))

=IF(COUNTIF($A$3:$A$7,$A$22),INDEX(B$3:B$7,MATCH($A$22,$A$3:$A$7,0)),"")

 

[mark5767]

Thanks, Biff, that's helpful conceptually to understand where the error is being generated and trapping it with greater precision.

One example I would imagine where IFERRROR would be always more efficient than ISERROR would be a simple VLOOKUP like I have set up in the past in Excel 2003 to avoid returning errors like this:

=IF(ISERROR(VLOOKUP($A22,$A$3:$C$7,B$1,FALSE))," ",VLOOKUP($A22,$A$3:$C$7,B$1,FALSE))

Replacing this with IFERROR I think would always be more efficient. Again I can't test it in Excel 2007 at the moment, but I thought I would present it as a potential instance where IFERROR is the hands down winner for simplicity. Thanks for your responses!

 

[VoG]

Try (in Excel 2007)

=IFERROR(VLOOKUP($A22,$A$3:$C$7,B$1,FALSE),"")

 

[T. Valko]

Thanks, Biff, that's helpful conceptually to understand where the error is being generated and trapping it with greater precision.

One example I would imagine where IFERRROR would be always more efficient than ISERROR would be a simple VLOOKUP like I have set up in the past in Excel 2003 to avoid returning errors like this:


=IF(ISERROR(VLOOKUP($A22,$A$3:$C$7,B$1,FALSE))," ",VLOOKUP($A22,$A$3:$C$7,B$1,FALSE))

Replacing this with IFERROR I think would always be more efficient. Again I can't test it in Excel 2007 at the moment, but I thought I would present it as a potential instance where IFERROR is the hands down winner for simplicity. Thanks for your responses!

Yes, in this application the IFERROR version is more efficient.

With ISERROR, if there is no error detected then the formula has to calculate the lookup twice. With IFERROR it only calculates the lookup once.

Another tip for reducing formula length...

For the range_lookup argument you can use 0 for FALSE and 1 (or omitted) for TRUE.

=IFERROR(VLOOKUP($A22,$A$3:$C$7,B$1,0),"")

 

[shg]

Another tip for reducing formula length...

For the range_lookup argument you can use 0 for FALSE

Indeed you could. You could equally replace -1 as the last argument in a MATCH function with TRUE, but why would you want to, in either case?

 

[T. Valko]

Indeed you could. You could equally replace -1 as the last argument in a MATCH function with TRUE, but why would you want to, in either case?

I don't know about you but I like to type as little as possible.

This falls into the same category as the "BigNum" argument!